Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Power BI Header Change

Power BI Header Update

Read about the exciting updates for the Power BI forum.