Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Writing DAX for comparing result of #221

Open
sairamunir1 opened this issue Oct 9, 2022 · 7 comments
Open

Writing DAX for comparing result of #221

sairamunir1 opened this issue Oct 9, 2022 · 7 comments

Comments

@sairamunir1
Copy link

sairamunir1 commented Oct 9, 2022

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
Copy link

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

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

Jiruiyang commented May 28, 2024 via email

@Hoopeu
Copy link

Hoopeu commented May 28, 2024 via email

@lingbaoer
Copy link

lingbaoer commented May 28, 2024 via email

@JR00010
Copy link

JR00010 commented May 28, 2024 via email

@Lifewords
Copy link

I'm learning

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
None yet
Development

No branches or pull requests

8 participants