Forum Discussion
Same Value For All Months
Hello,
Here is my fact table:
| Dimension | Value | Execution Period |
| Execution | 266,846,836 | |
| Execution | 286,169,980 | |
| Execution | 324,761,947 | |
| Execution | 81,715,856 | 01/12/2021 |
| Execution | 74,927,028 | 01/11/2021 |
| Execution | 85,850,086 | 01/10/2021 |
| Execution | 89,286,662 | 01/09/2021 |
| Execution | 61,884,045 | 01/08/2021 |
| Execution | 32,137,270 | 01/07/2021 |
| Closing | 610,931,927 | 01/06/2021 |
Here is the DAX for my calendar:
VAR _minPeriod = min(CheatSheet[Reporting Period])-365
VAR _maxPeriod = max(CheatSheet[Reporting Period])+365
RETURN
ADDCOLUMNS (
calendar(_minPeriod,_maxPeriod),
"Calendar Year",YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmm" ),
"Month Number", MONTH ( [Date] ),
"Text Year", format([Date],"YYYY-MM"),
"Abbrreviation", format([Date],"MMM 'YY"),
"Number Year", value(format([Date],"YYYYMM"))
)
1) I want to create few measures, the first is to show the accumulated execution - I was trying to do:
Burndown =
VAR _period = selectedvalue('Calendar'[Number Year])
VAR _burndownBacklog = calculate([Backlog Execution],
filter(
all('Calendar'),
'Calendar'[Number Year]<=_period)
RETURN
_burndownBacklog
I am applying this in a table visual with the column "Abbreviation". For some reason, this measure is returning all the "Abbreviation" that are in the Calendar table. I would like to return only the ones that are included in the fact table on an increasing basis.
Jul '21 = 32,137
Aug '21 = 94,021
Sep '21 = 183,308
etc...
2) I would like also to create a measure that will deduct the BURNDOWN measure from the CLOSING measure (CLOSING= calculate(sum(Backlog[Value]), Backlog[Dimension]="Closing Backlog").
So, my expected result would be:
Jul '21 = 578,795 (CLOSING - BURNDOWN Jul '21)
Aug '21 = 516,911 (Jul '21 above - BURNDOWN Aug '21)
Sep '21 = 427,624 (Aug '21 above - BURNDOWN Sep '21)
...etc
Is there a way to do this based on measures?
1 Reply
Anonymous , Hope you are creating a measure , and Backlog Execution is a measure too
try
a new measure
Burndown =
VAR _period = max('Calendar'[Date])
VAR _burndownBacklog = calculate([Backlog Execution],
filter(
all('Calendar'),
'Calendar'[Date]<=_period))
RETURN
_burndownBacklog
