Forum Discussion
Responsive ResidentDAX Help: Running YTD Total by Day
Looking for help on this Dax Measure I wrote. Below is the Measure that Needs Adjusting
Note - Company Fiscal Year is May - April
PY Net Sales = SWITCH([Selected Period],
"MTD", CALCULATE(SUM(Source_TerritorySales_FY25[sale_net]),Dim_Date[Fiscal Year]=2024),
"YTD", CALCULATE(SUM(Source_TerritorySales_FY25[sale_net]),Dim_Date[Fiscal Year]=2024,DATESYTD(DATEADD(Dim_Date[Date],-1,YEAR),"4/30")),
BLANK() )
I have a two slicers, one were End-Users can select MTD/YTD, and a Slicer to Select a Month
The new request is when a End-User selects 'YTD' and the month 'Aug', for example, that the net sales value would show 5/1/2023 - today (8/8/2023).
What the current measure is showing is 5/1/2023 - 8/31/2023.
Any suggestions?
Hi,
Thanks for the solution Greg_Deckler and 123abc offered and i want to offer some more information for user to refer to.
hello IamTDR , you can create the following measure
MEASURE = VAR a = CALCULATE ( MIN ( Dim_Date[Date] ), ALL ( Dim_Date ), Dim_Date[Fiscal Year] = 2024 ) RETURN IF ( SUM ( 'Table'[sales_net] ) <> BLANK () && MAX ( Dim_Date[Fiscal Year] ) = 2024, CALCULATE ( SUM ( 'Table'[sales_net] ), FILTER ( Dim_Date, [Date] >= a && Dim_Date[Date] <= EDATE ( TODAY (), -12 ) ) ) )Ouptut
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- 123abc
Community Champion
The key here is to use MIN or MAX on the selected date from your slicer and compare it to TODAY() to ensure the YTD calculation is limited to the current date if it's within the selected month.
Plz try this measure:
PY Net Sales =
SWITCH(
[Selected Period],
"MTD",
CALCULATE(
SUM(Source_TerritorySales_FY25[sale_net]),
Dim_Date[Fiscal Year] = 2024
),
"YTD",
CALCULATE(
SUM(Source_TerritorySales_FY25[sale_net]),
Dim_Date[Fiscal Year] = 2024,
DATESBETWEEN(
Dim_Date[Date],
STARTOFYEAR(Dim_Date[Date], "4/30"),
MIN(TODAY(), MAX(Dim_Date[Date]))
)
),
BLANK()
)Example:
- If the user selects "YTD" and the month "August", and today is "8/8/2023", the measure will sum up the sales from "5/1/2023" to "8/8/2023".
- If the user selects a future month, e.g., "September," the measure will sum from "5/1/2023" to "8/8/2023" (today) because TODAY() is earlier than the end of September.
IamTDRThanks for the reply
I am not getting the desired outcome with the proposed measure.
Adding a screenshot of some sample data.
So if my End-User is Selecting the Month 'AUG' and they want to switch between MTD and YTD- Greg_Deckler
IamTDR Try this: Better Year to Date Total - Microsoft Fabric Community
And this video demonstrates a variety of ways to do what you are trying to do:
