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.

[Ganeshdevatha523]

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

[Jiruiyang]

来信收到，谢谢，祝您一切顺利！

[Hoopeu]

邮件已收到，我会尽快回复。

[lingbaoer]

您好，您的邮件我已收到！祝工作顺利，天天开心。。。

[JR00010]

这是来自QQ邮箱的假期自动回复邮件。你好，我最近正在休假中，无法亲自回复你的邮件。我将在假期结束后，尽快给你回复。

[Lifewords]

I'm learning

[Dhanashree-Kalyani]

// Total trips for the first week of the previous month
FirstWeekPreviousMonthTrips =
VAR FirstDayPreviousMonth = EOMONTH(TODAY(), -2) + 1
VAR LastDayFirstWeekPreviousMonth = FirstDayPreviousMonth + 6
RETURN
CALCULATE(
SUM('Trips'[TripCount]),
'Date'[Date] >= FirstDayPreviousMonth,
'Date'[Date] <= LastDayFirstWeekPreviousMonth
)

// Total trips for the first week of the current month
FirstWeekCurrentMonthTrips =
VAR FirstDayCurrentMonth = EOMONTH(TODAY(), -1) + 1
VAR LastDayFirstWeekCurrentMonth = FirstDayCurrentMonth + 6
RETURN
CALCULATE(
SUM('Trips'[TripCount]),
'Date'[Date] >= FirstDayCurrentMonth,
'Date'[Date] <= LastDayFirstWeekCurrentMonth
)

// Comparison of the first week's trips for the current and previous months
ComparisonTrips =
DIVIDE(
[FirstWeekCurrentMonthTrips],
[FirstWeekPreviousMonthTrips],
0 // If the denominator is 0, return 0
)