Writing DAX for comparing result of

sairamunir1 commented 1 year ago

Can someone send how to write dynamic code in DAX to retrieve first week of data for previous month to compare it to first week of data of current month.

For example . i want to find out total trips of a car in 1-Sep-2022 to 7 -Sep-2022 to total trips of a car in 1 -Oct 2022 to 7-Oct 2022

Ziniddoug commented 1 year ago

// Get the current date
VAR CurrentDate = TODAY()

// Get the date of the first day of the current month
VAR FirstDayCurrentMonth = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1)

// Get the date of the first day of the previous month
VAR FirstDayPreviousMonth = EOMONTH(FirstDayCurrentMonth, -1) + 1

// Get the date of the last day of the first week of the current month
VAR LastDayFirstWeekCurrentMonth = FirstDayCurrentMonth + WEEKDAY(FirstDayCurrentMonth) - 1

// Get the date of the last day of the first week of the previous month
VAR LastDayFirstWeekPreviousMonth = FirstDayPreviousMonth + WEEKDAY(FirstDayPreviousMonth) - 1

// Calculate the total trips for the first week of the current month
VAR TotalTripsCurrentMonth = CALCULATE(SUM(TripsTable[Trips]), TripsTable[Date] >= FirstDayCurrentMonth && TripsTable[Date] <= LastDayFirstWeekCurrentMonth)

// Calculate the total trips for the first week of the previous month
VAR TotalTripsPreviousMonth = CALCULATE(SUM(TripsTable[Trips]), TripsTable[Date] >= FirstDayPreviousMonth && TripsTable[Date] <= LastDayFirstWeekPreviousMonth)

RETURN
    TotalTripsCurrentMonth - TotalTripsPreviousMonth

In this example, we assume that you have a table named "TripsTable" with a column named "Date" that contains the dates of the trips and a column named "Trips" that contains the number of trips.

You can adjust the code above according to your actual table name and relevant column names. It will return the difference between the total trips for the first week of the current month and the total trips for the first week of the previous month.

Make sure to replace "TripsTable" with the correct name of your table and "Trips" with the correct name of the column that contains the number of trips.

Ganeshdevatha523 commented 4 months ago

// Get the current date VAR CurrentDate = TODAY()

// Get the date of the first day of the current month VAR FirstDayCurrentMonth = DATE(YEAR(CurrentDate), MONTH(CurrentDate), 1)

// Get the date of the first day of the previous month VAR FirstDayPreviousMonth = EOMONTH(FirstDayCurrentMonth, -1) + 1

// Get the date of the last day of the first week of the current month VAR LastDayFirstWeekCurrentMonth = FirstDayCurrentMonth + 6

// Get the date of the last day of the first week of the previous month VAR LastDayFirstWeekPreviousMonth = FirstDayPreviousMonth + 6

// Calculate the total trips for the first week of the current month VAR TotalTripsCurrentMonth = CALCULATE( SUM(TripsTable[Trips]), TripsTable[Date] >= FirstDayCurrentMonth && TripsTable[Date] <= LastDayFirstWeekCurrentMonth )

// Calculate the total trips for the first week of the previous month VAR TotalTripsPreviousMonth = CALCULATE( SUM(TripsTable[Trips]), TripsTable[Date] >= FirstDayPreviousMonth && TripsTable[Date] <= LastDayFirstWeekPreviousMonth )

// Return the difference in total trips between the first week of the current month and the first week of the previous month RETURN TotalTripsCurrentMonth - TotalTripsPreviousMonth