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.
How are you, I hope you can help me... I have the requirement to make a cumulative over time, I have a table of activities that relates directly to the calendar table by the date of the activity. It's not every day that there are activities. However, in the table they are expected to be seen every day. When a day has no values, the previous value must be displayed. And once the maximum date of the activity for that calendar, cycle, year and brand is reached, the calculation must be stopped. In the image, AC Curves Activities performs the cumulative in the correct period, that is, from the minimum date of activity to the maximum date, however it leaves empty spaces on the days when there is no activity. On the right side we have Activities Ac1 and Ac3 that perform the cumulative including the days without activity and repeat the previous value but should stop up to the maximum value of the activity. That is, it should be displayed until Feb. 22. and from then on show nothing. I've tried different alternatives and can't find how to adjust the metric.
The metrics in question:
Solved! Go to Solution.
@Syndicate_Admin , If you need cumulative with date/calendar table, use meausre like
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))
Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))
Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))
Use Column date table in measure, visual and slicer
If you need the last-day value for a simple sum
Cumm Sales = CALCULATE(lastnonblankvalue('Date'[Date]), SUM(Sales[Sales Amount])) ,filter(allselected(date),date[date] <=max(date[Date])))
@Syndicate_Admin , If you need cumulative with date/calendar table, use meausre like
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(all('Date'),'Date'[date] <=max('Date'[date])))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[Date])))
Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALL('date'[date]),ORDERBY('Date'[date],ASC)))
Cumm Based on Date = CALCULATE([Net], Window(1,ABS,0,REL, ALLSELECTED('date'[date]),ORDERBY('Date'[date],ASC)))
Use Column date table in measure, visual and slicer
If you need the last-day value for a simple sum
Cumm Sales = CALCULATE(lastnonblankvalue('Date'[Date]), SUM(Sales[Sales Amount])) ,filter(allselected(date),date[date] <=max(date[Date])))
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |