cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## 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
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?
Super User

@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