Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Multiple columns cannot be converted to a scalar value
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.
Please do let me know if anyone here helps me how to solve this
Hello , any solution for this query?
What is the error message ?
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] )
)
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
84 | |
39 | |
25 | |
21 |