Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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??
User | Count |
---|---|
96 | |
85 | |
77 | |
66 | |
63 |
User | Count |
---|---|
110 | |
96 | |
96 | |
67 | |
59 |