topic Re: Previous Month Sales with Month Selected in Slicer in DAX Commands and Tips

Previous Month Sales with Month Selected in Slicer

Sid8686 — Mon, 07 Aug 2023 22:12:59 GMT

Hi All,

I got stuck with a DAX measure.

I need to calculate the Current Month's sales and the Previous Month's sales. Both the measures are working perfectly if nothing is selected in the date or month slicer. when I select a month in the slicer then the Previous Month's sales measure is giving a blank.

For example, if I select "June" in the slicer table then the Current Month's sales measure should be of June and the Previous Month's sales measure should be of May.

My measure are given below:

Current Month Sales =
TOTALMTD (
SUM ( 'Dataset supply chain'[total sale] ),
DATESMTD ( 'Dataset supply chain'[Order date] ))

Previous Month Sales =
CALCULATE (
SUM ( 'Dataset supply chain'[total sale] ),
PREVIOUSMONTH ( DATESMTD ( 'Dataset supply chain'[Order date] ) ))

I am also attaching screenshots of the results.

Looking forward to a solution.

Thanks,

Sid

Re: Previous Month Sales with Month Selected in Slicer

johnt75 — Tue, 08 Aug 2023 10:12:32 GMT

Firstly, create a proper date table and link it to your fact table. Then you can create measures like

Current Month Sales = TOTALMTD ( SUM ( 'Dataset supply chain'[total sale] ), 'Date'[Date] ) Previous Month Sales = CALCULATE ( SUM ( 'Dataset supply chain'[total sale] ), PREVIOUSMONTH ( 'Date'[Date] ) )

Re: Previous Month Sales with Month Selected in Slicer

Sid8686 — Thu, 10 Aug 2023 00:39:52 GMT

Thanks, it works.
One more question regarding the same visual. how i can calculate the TOP 5 products (by sales) of the Current Month and the Top 5 products (by sales) of the Previous Month? I am trying the above measures with the TOPN function but it is giving me error.

Re: Previous Month Sales with Month Selected in Slicer

johnt75 — Thu, 10 Aug 2023 08:24:46 GMT

What is the error message ?

Re: Previous Month Sales with Month Selected in Slicer

Sid8686 — Thu, 10 Aug 2023 11:04:40 GMT

Multiple columns cannot be converted to a scalar value

Re: Previous Month Sales with Month Selected in Slicer

Sid8686 — Fri, 11 Aug 2023 21:01:16 GMT

Hello , any solution for this query?

Re: Previous Month Sales with Month Selected in Slicer

Sid8686 — Mon, 14 Aug 2023 03:49:24 GMT

Please do let me know if anyone here helps me how to solve this