Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
I have this small example data table:
PostingDate | EoM | Material | Quantity |
12-01-21 | 31-01-21 | abc | 50 |
23-01-21 | 31-01-21 | abc | 75 |
08-02-21 | 28-02-21 | abc | 25 |
25-02-21 | 28-02-21 | abc | 100 |
01-04-21 | 30-04-21 | abc | 200 |
29-04-21 | 30-04-21 | abc | 65 |
15-01-22 | 31-01-22 | abc | 150 |
17-01-22 | 31-01-22 | abc | 35 |
10-02-22 | 28-02-22 | abc | 85 |
15-02-22 | 28-02-22 | abc | 115 |
03-04-22 | 30-04-22 | abc | 155 |
19-04-22 | 30-04-22 | abc | 95 |
05-03-21 | 31-03-21 | def | 10 |
05-03-22 | 31-03-22 | def | 35 |
Now I created a YTD measure and want to show it over time by period (for now I put it in a matrix):
So the problem here is that period 3 shows no data for material abc.
All the numbers are fine and also the total is fine but I would like to have period 3 populate despite that there was no action.
What period 3 should show is the same ending balance as period 2.
So that when you would put things in a chart there would not be a broken line or en empty bar/column.
Guess if it is possible it would need to be done by DAX.
So how would I tell it to show the same YTD end balance as previous month if nothing happend in the "current" month?
This is my YTD measure now:
Solved! Go to Solution.
Try this.
Total = CALCULATE(SUM('Table'[Quantity]), FILTER(ALLEXCEPT(Calender,'Calender'[Date].[Year]), 'Calender'[Date] <= MAX('Calender'[Date])))
Yes I think I again was using something similar which seems to work the same:
Hi @Dinesh_Suranga ,
Nope tried that. That does not work :
Just adds a lot of extra empty columns.
And you cannot use the "select items with no data" on a measure.
Yes I do.
Yes I was using now the TOTALYTD function:
Try this.
Total = CALCULATE(SUM('Table'[Quantity]), FILTER(ALLEXCEPT(Calender,'Calender'[Date].[Year]), 'Calender'[Date] <= MAX('Calender'[Date])))
Yes I think I again was using something similar which seems to work the same:
User | Count |
---|---|
84 | |
71 | |
71 | |
68 | |
55 |
User | Count |
---|---|
94 | |
92 | |
91 | |
77 | |
70 |