Writing DAX for comparing result of

[sairamunir1]

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]

// 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.