Date range slicer DAX help request

NT92 · ‎02-12-2022

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 calculate for the below listed in DAX could you please anyone help me

I'm New to DAX finding difficult to calculate the below

Data model:

Date table connected with 1 side to many side of sales table

Disconnected Type slicer table

Case 1: IF Year month slicer & type slicer if user selected the month then it should show the running total of sales amount till max of selected month

Case 2: IF Year month slicer & type slicer is blank then it should show the running total of sales amount from latest year from Jan to max month

Case 3: IF Year month slicer & type slicer is blank then it should show the month of sales amount from latest year Jan to max month

Case 4: IF Year month slicer & type slicer whichever month the user selected then it should show that month sales amount only

Case 5: IF Year month slicer & type slicer is blank then it should show the sales amount from latest year from Jan to max month

Case 6: IF Year month slicer & type slicer is blank then it should show the sales amount of last 12 months from selected month

urgent request can anyone please help me

Thank you.

NT92 · ‎02-12-2022

Hi @littlemojopuppy

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

Please help

Thank you.

littlemojopuppy · ‎02-12-2022

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

littlemojopuppy · ‎02-12-2022

Hi @NT92

What you can do is create a measure that works like this...

SWITCH(
	SELECTEDVALUE(DisconnectedSlicerTable[FieldName]),
	"YTD", [Measure for YTD],
	"Selected Month", [Measure for Selected Month]
	--Repeat as necessary
)

Hope this helps!