Skip to main content
cancel
Showing results for 
Search instead 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

Reply
Anonymous
Not applicable

Same Value For All Months

Hello,

 

Here is my fact table:

 

DimensionValueExecution Period 
Execution266,846,836 
Execution286,169,980 
Execution324,761,947 
Execution81,715,85601/12/2021
Execution74,927,02801/11/2021
Execution85,850,08601/10/2021
Execution89,286,66201/09/2021
Execution61,884,04501/08/2021
Execution32,137,27001/07/2021
Closing610,931,92701/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 1
amitchandak
Super User
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

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.