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,
I am trying to write a measure which can calculate the running totals across months of various years. I am somehow getting accurate result with my formula but the only problem with my formula is that it is calculating or rather copy pasting the same amount in the months that are not present in my data.
My data has sales till 31-01-2016 but somehow the running total formula which i wrote is calculating the running total for feb ,march & so on till december but copy pasting the january sales total in all months.
here is what i tried.
Runningtotal_YTD = calculate(sale,datesytd(datekey(date)))
I also tried
runningtotal_YTD= Totalytd(sales,datekey(date)).
Both formula are fetching the identical results.
How can i resolve it??
Solved! Go to Solution.
@hemantsingh you could try something like this
Runningtotal_YTD = IF ( MIN( datekey(date) <= MAX(facttable[datecolumn]); calculate(sale,datesytd(datekey(date))) )
@hemantsingh you could try something like this
Runningtotal_YTD = IF ( MIN( datekey(date) <= MAX(facttable[datecolumn]); calculate(sale,datesytd(datekey(date))) )
The easiest way to solve the problem is to make sure your calendar table doesn't extend past your data. I wrote a blog about it here.
http://www.powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/
My sincere thanks for reverting to my both queries. I read your blog & it is really helpful but it is a solution in power query whereas my Problem is that i have already created a date table within DAX & has made numerous calculated column too under it. Can you suggest some way to dynamically control end date in within Power BI using DAX??
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 |
---|---|
107 | |
106 | |
87 | |
73 | |
66 |
User | Count |
---|---|
124 | |
113 | |
98 | |
81 | |
72 |