topic Re: Sales of the last quarter when using slicer by day of the month in DAX Commands and Tips

Sales of the last quarter when using slicer by day of the month

Laocsulak — Tue, 17 Jan 2023 15:28:31 GMT

Hello,

I have created a measure that show the sales of the last quarter and works fine, but when I use a slicer to filter by the number day of the month it keeps showing the sales of the entire month. Does anyone know how to adapt the measure to fix this?

Example:

This shows correctly the sales of the last quarter

But for example if I filter to see the sales for just the first 10 days for every month of every quarter, it keeps showing the same information in the sales in the last quarter, and that's not correct.

Measure craeted:

Sales LQ =

VAR __PREV_QUARTER =

CALCULATE ( [total sales] , PREVIOUSQUARTER(Calendario[Date]) )

RETURN

IF (

ISBLANK ( __PREV_QUARTER ),

BLANK (),

( __PREV_QUARTER

))

Thanks in advance!

Sample:

https://drive.google.com/drive/folders/1TOYezhAuiy0ADTNcKVL1ayNw5XoLMo_I?usp=share_link

Re: Sales of the last quarter when using slicer by day of the month

amitchandak — Tue, 17 Jan 2023 15:58:00 GMT

Laocsulak , Try if measure like this can help

trailing QTR = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,QUARTER))

Re: Sales of the last quarter when using slicer by day of the month

Laocsulak — Tue, 17 Jan 2023 16:07:59 GMT

I have tried that measure before, but when the quarter has not ended, it doesn't show the right amount for the previous quarter. In this example, it shows 10 instead of 24 in the last quarter.

Re: Sales of the last quarter when using slicer by day of the month

Anonymous — Wed, 18 Jan 2023 02:41:08 GMT

Hi Laocsulak ,

Please have a try.

Create columns and measures.

QUANTER = VAR _year = YEAR ( Sheet1[created] ) VAR _q = QUARTER ( Sheet1[created] ) RETURN _year & "/" & "Q" & _q

DAYS = DAY(Sheet1[created])

Measure:

Measure = CALCULATE ( SUM ( Sheet1[amount] ), FILTER ( ALL ( Sheet1 ), Sheet1[DAYS] <= MAXX ( ALLSELECTED ( Calendario ), Calendario[Dia] ) && Sheet1[QUANTER] = SELECTEDVALUE ( Calendario[Año/trimestre] ) ) )

How to Get Your Question Answered Quickly

If it still does not help,Please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Sales of the last quarter when using slicer by day of the month

Laocsulak — Wed, 18 Jan 2023 13:06:31 GMT

Thanks for the answer, although I have tried that and it shows the sales of the current quarter, I need to show the sales of the previous quarter, and also it's not affected by the slicer.

Re: Sales of the last quarter when using slicer by day of the month

Anonymous — Thu, 19 Jan 2023 01:36:37 GMT

Hi Laocsulak ,

Please have a try.

Change the quanter column.

QUANTER = var _year=YEAR(Sheet1[created]) var _year1=YEAR(Sheet1[created])-1 var _q=QUARTER(Sheet1[created]) var _qa=QUARTER(Sheet1[created])-1 var _1re= _year&"/"&"Q"&_qa return IF(_q=1,_year1&"/"&"Q"&4,_1re)

and it is affected by the slicer.

If it still does not help,Please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.