Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It 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 |
---|---|
106 | |
89 | |
81 | |
76 | |
73 |
User | Count |
---|---|
112 | |
104 | |
96 | |
74 | |
66 |