Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
There's a table with sales figures per day as rows. I need to get the sum of sales of the latest month only and show it in a card visual.
I'm a noob with DAX so can you please help me figure this out?
Solved! Go to Solution.
Hi @Enigma ,
You also could use the following dax:
meausre = var first= EOMONTH(MAX('Table'[date]),-1)
var second=CALCULATE (
AVERAGE( 'Table'[value] ),
ALL ( 'Table' ),
'Table'[date]>first
)
return second
the two measure return the same result:
Wish it is helpful for you!
Best Regards
Lucien
Hi @Enigma ,
You also could use the following dax:
meausre = var first= EOMONTH(MAX('Table'[date]),-1)
var second=CALCULATE (
AVERAGE( 'Table'[value] ),
ALL ( 'Table' ),
'Table'[date]>first
)
return second
the two measure return the same result:
Wish it is helpful for you!
Best Regards
Lucien
Thanks for helping out!
@ERD - I had checked that page before and it was not exactly what I needed.
@v-luwang-msft - I only need the score for the latest month and not the YTD.
I wrote the following and it worked.
AvgQCScore Latest Month =
VAR latestMonth = MONTH(MAX(tblSherlockRaw[Case End Date]))
VAR latestYear = YEAR(MAX(tblSherlockRaw[Case End Date]))
RETURN
CALCULATE(
AVERAGE(tblSherlockRaw[TOTAL_QC_SCORE]),
MONTH(tblSherlockRaw[Case End Date]) = latestMonth,
YEAR(tblSherlockRaw[Case End Date]) = latestYear
)
Any improvements you can suggest in the above?
Hi @Enigma ,
You could use the below measure:
measure =
CALCULATE (
SUM ( 'Table'[value] ),
ALL ( 'Table' ),
'Table'[date]
>= IF (
MONTH ( TODAY () ) > 1,
DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ) - 1, DAY ( TODAY () ) ),
DATE ( YEAR ( TODAY () ) - 1, 12, DAY ( TODAY () ) )
)
)
the base data:
and final you will get:
You want get the the sum of sales of the latest month , in my measure ,i will get the sum from 2021/3/19 to today,
and when actual data is January, you will get the sum from last December to January.
Wish it is helpful for you!
Best Regards
Lucien
Hi, @Enigma ,
Have a look at this: https://expert-only.net/dax-functions/calculate-last-month-value-dax-power-bi/
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.
Check out my latest demo report in the data story gallery.
Stand with Ukraine!
Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/
Thank you!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |