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.
Good morning,
Looking for a rolling 12 month and/or a rolling 365 days of sales, however, if the month is not complete then default to previous months rolling 12 month value. I've tried several methods on this forum, but nothing seems to be producing the desired result. Not sure if anyone has any tips to help solve this. Thank you!!!
Solved! Go to Solution.
@Jkaelin,
Create the following measures and check if you get expected result.
last month of last year = CALCULATE([Rolling 12M Sales],FILTER(ALL('Master_Calendar'),'Master_Calendar'[Year]=YEAR(TODAY())-1 && 'Master_Calendar'[MonthOfYear]=12))
Expected result = IF(YEAR(TODAY())>MAX('Master_Calendar'[Year])&&TODAY()>EOMONTH(MAX('Master_Calendar'[Date]),0),[Rolling 12M Sales],[last month of last year])
Regards,
Lydia
Can you post your formula? I'm guessing it can be fixed with an IF statement and a CALCULATE with a MAX filter but can't know for sure.
Hey Smoupre! This is one of my formula's. I've tried a variety of IF, THEN statements to no avail.
Rolling 12M Sales :=
CALCULATE (
[Total Revenue],
DATESBETWEEN (
'Master_Calendar'[Date],
NEXTDAY ( SAMEPERIODLASTYEAR ( LASTDATE ( 'Master_Calendar'[Date] ) ) ),
LASTDATE ( 'Master_Calendar'[Date] )
)
This formula was posted by @Vvelarde in another related thread, but it would error out on me.
ValueCalc = if(TODAY()>=EOMONTH(VALUES(Table1[DateRef]);0);EXPRTRUE;EXPRFALSE)
This seems like it should be easy to do but then I can't do it, makes me feel like I have no understanding of Row/Filter context. 😞
So, is the main table you are dealing with 'Master_Calendar' or some other table? And if some other table, what is filtering dates from master_calendar, a relationship?
The main table is my factSales table. My main calendar table is 'Master_Calendar'. Master Calendar is a one-to-many to the factSales table. Any ideas on a good approach?
Kindly - James
@Jkaelin,
Create the following measures and check if you get expected result.
last month of last year = CALCULATE([Rolling 12M Sales],FILTER(ALL('Master_Calendar'),'Master_Calendar'[Year]=YEAR(TODAY())-1 && 'Master_Calendar'[MonthOfYear]=12))
Expected result = IF(YEAR(TODAY())>MAX('Master_Calendar'[Year])&&TODAY()>EOMONTH(MAX('Master_Calendar'[Date]),0),[Rolling 12M Sales],[last month of last year])
Regards,
Lydia
Thank you for your help. Sorry for delay in responding, I been under the weather. The measures you created work and appears to do the trick. 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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
147 | |
106 | |
104 | |
87 | |
61 |