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.
Hello Everyone ,
I have an issue using PreviousMonth DAX. Please help me.
I have a requirement to show current and Previous rate in Report. Previous Rate here refers to Previous Reporting Date. I have 10 Reporting Dates in my Report Jan to October .
So, i used below formula for calculating Prior rate.
Prior Rate=
var a=PREVIOUSMONTH(ReportDate)
var b=calculate(sum(Rate), Report Date=a)
return
b
But the issue is when i select max reporting Date i.e 10/31/2020 , iam getting error that "A Table of multiple values was supplied, whereas single value is expected". This issue is coming only for max Report Date i.e 10/31/2020
So, Please help me to solve this.
TIA
Hi @Vamshi2020
Please refer to below remark of PREVIOUSMONTH() function and check when you select the max Report Date, whether your table has multiple Report Dates in its previous month? In the code 'var b=calculate(sum(Rate), Report Date=a)', the value after '=' is expected to be a single value.
Kindly let me know if this helps.
Community Support Team _ Jing Zhang
If this post helps, please consider Accept it as the solution to help other members find it.
@Vamshi2020 , Try like
Prior Rate=
calculate(sum(Table[Rate]), PREVIOUSMONTH(Table[Report Date]))
Prefer to use date table
example
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value = CALCULATE(sum('Table'[total hours value]),previousmonth('Date'[Date]))
refer: https://www.youtube.com/watch?v=6LUBbvcxtKA
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.
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 |
---|---|
111 | |
97 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |