Forum Discussion

NT92's avatar
NT92
Regular Visitor
4 years ago

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

3 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    4 years ago

    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!

  • NT92's avatar
    NT92
    Regular Visitor
    4 years ago

    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's avatar
      littlemojopuppy
      Community Champion
      4 years ago

      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.