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,
I have a Dataset named PA with two columns: Date, and Total Sales. Right now, I have a measure that filters based the following:
Solved! Go to Solution.
@Anonymous Are you missing the second part of your IF statement? May be easier to see here, use daxformatter.com:
TEST =
VAR __Calc =
CALCULATE (
SUM ( PA[Sales Amount] ) / SUM ( PA[Split per Year 2021] ),
FILTER ( PA, MONTH ( PA[Date Snapshot] ) = MONTH ( TODAY () ) )
)
RETURN
IF (
ISBLANK ( __Calc ),
CALCULATE (
SUM ( PA[Sales Amount] ) / SUM ( PA[Split per Year 2021] ),
FILTER ( PA, MONTH ( PA[Date Snapshot] ) = MONTH ( TODAY () - 1 ) )
), __Calc
)
@Anonymous , see if you can use time intelligence with date table
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
LMTD= CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
LMTD -2= CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-2,MONTH)))
previous Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
previous (complete) Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month = CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))
Now
if(isblank([MTD]), [LMTD],[MTD])
same way for last month
if(isblank([MTD]), [LMTD-2],[LMTD])
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 :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
@Anonymous Maybe try:
Measure =
VAR __Calc = <your calc>
RETURN
IF(ISBLANK(__Calc),<your calc with [Date Snapshot] = MONTH(TODAY())-1>
?
Can be more specific with sample data.
hi @Greg_Deckler,
Thank you for your answer. I tried using what you wrote and I got the final code below. Unfortunately, it shows a Blank Value.
HI @Anonymous,
Did Greg_Deckler works on your side? If not, please share some dummy data then we can test to coding formula on them.
How to Get Your Question Answered Quickly
Regards,
Xiaoxin Sheng
@Anonymous Are you missing the second part of your IF statement? May be easier to see here, use daxformatter.com:
TEST =
VAR __Calc =
CALCULATE (
SUM ( PA[Sales Amount] ) / SUM ( PA[Split per Year 2021] ),
FILTER ( PA, MONTH ( PA[Date Snapshot] ) = MONTH ( TODAY () ) )
)
RETURN
IF (
ISBLANK ( __Calc ),
CALCULATE (
SUM ( PA[Sales Amount] ) / SUM ( PA[Split per Year 2021] ),
FILTER ( PA, MONTH ( PA[Date Snapshot] ) = MONTH ( TODAY () - 1 ) )
), __Calc
)
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 |
---|---|
110 | |
97 | |
78 | |
64 | |
55 |
User | Count |
---|---|
143 | |
109 | |
89 | |
84 | |
66 |