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,
I need to create a measure in Power BI which calculates Sales Value for the Year to Date but split out by period. I've created the table I'm aiming for in Excel for clarification, and I'm having difficulty with the 'YTD' column:
Can anybody enlighten me? It's driving me mad. I have a good calendar table with dates in the format dd-mm-yyyy and also periods in the format yyyymm.
Thanks!
Solved! Go to Solution.
OK. That's why TOTALYTD is not working.
Try this then:
CALCULATE([Period],FILTER(ALL(Fact),Fact[Date]<=MAX(Fact[Date]) && Fact[Year]=MAX(Fact[Year]))
Michael
Try YTD = TOTALYTD([Period],CalendarTable[Date])
Where [Period] is your usual "SUM" measure
This gives me the same values in my YTD measure as in my Period measure when I make the matrix table, with Period in columns, Company Name in rows, and Sales (Period) and Sales (YTD) (measures) in Values
1. Make sure you put a Period column from your Calendar table on columns
2. Make sure you defined a relationship between Calendar Table and Fact table
3. If this doesn't work - send me a link to your .pbix file I'll take a look
Michael
I haven't had to use the calendar table in the end. I have a date column in my Sales table which aligns to the period i.e. 201601 = 01-01-2016. SO I don't actually use the calendar table at all. Only Sales (for prd), Sales (YTD), Date and CompanyName. When I put Date on columns it only shows me the years, nothing more granular
OK. That's why TOTALYTD is not working.
Try this then:
CALCULATE([Period],FILTER(ALL(Fact),Fact[Date]<=MAX(Fact[Date]) && Fact[Year]=MAX(Fact[Year]))
Michael
Thanks @Anonymous! Your formula largely gave me what I was looking for. In the end I've used the following formula to allow me to split out by company:
CALCULATE([Period],FILTER(ALLEXCEPT(Fact, Fact[CompanyName]),Fact[Date]<=MAX(Fact[Date ]) && Fact[Year]=MAX(Fact[Year])))
Thanks for your help!!
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |