Forum Discussion
Date range slicer DAX help request
Hi , I’m developing a report in powerbi I have two slicers one slicer with Year& Month combined in Date hierarchy and another slicer with type as YTD & selected month & trailing12months I have to ca...
I have taken a copy of Date table added in the model as previous Date and set the relationship as many side of Date table to one side of previousdate table and computed the below measure however it is not working could you please provide the email ID of yours so that i can send you the actual PBIX file
YTD =
VAR ReferenceDate =MAX('Date'[Date])
VAR ReferencePrevdate=MAX('PreviousDate'[Date])
VAR PreviousDates=
FILTER(
DATESINPERIOD(
'PreviousDate'[Date],
ReferenceDate,
-12,
Month
),
'PreviousDate'[Date] <= ReferencePrevdate
)
VAR Result=
IF(
ReferencePrevdate <=ReferenceDate,
CALCULATE(
[Measure_Total Sales],
ALL('Date'),
PreviousDates,
USERELATIONSHIP('PreviousDate'[Date],'Date'[Date])
)
)
Return
Result
TTM =
VAR ReferenceDate=MAX('Date'[Date])
VAR PrevDate=DATESINPERIOD(
'PreviousDate'[Date],
ReferenceDate,
-12,MONTH)
VAR Result=CALCULATE(
SUM(Sales[Sales Amount]),
All('Date'),
KEEPFILTERS(PrevDate),
USERELATIONSHIP('Date'[Date],'PreviousDate'[Date])
)
Return
Result
Thank you.
NT92 you can upload your pbix to OneDrive or Google Drive and post a link for myself or another user to download. I'll take a look at it in the morning.