Same Value For All Months

[Anonymous]

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?

[amitchandak]

@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