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
13havya
Frequent Visitor

Dynamically calculate sum for current and historical data

Hi,

 

I am trying to develop a measure that returns the amount of money held in unresolved files. The metric needs to show the values at any point in time, and not just the live data. To facilitate that, we have merged a live data and cutoff data report in our fact table. 

The measure looks something like this 


$ Debt = 
VAR Live = CALCULATE (SUMX(FILTER(Fact table, report = "Live" && {conditions}), debt_amt)), userelationship(timestamp, date))

Var Historical = CALCULATE (SUMX(FILTER(Fact table, report = "Historical" && {conditions}), debt_amt)),userelationship(timestamp, date))

RETURN 

IF (YEAR(Max(Dim_Date[Date])) = YEAR(TODAY) && MONTH(MAX(Dim_Date[Date])) = MONTH(TODAY), Live, Historical)

 

The measure works as intended when viewing month by month, but the problem arises when trying to view it over different time dimensions i.e quarterly/half yearly basis - where it sums up the debt over the time period and returns a total value. 

However, I would like the measure to return the value at the last point in time if selected over a larger time period (i.e. if selected over a quarter, it should show the debt value at the last date in that quarter, and not add up the 3 months). 

 

Any tips and suggestions would be greatly appreciated. 

 

Thanks 

1 ACCEPTED SOLUTION
xifeng_L
Solution Sage
Solution Sage

Hi @13havya ,

 

You can try below measure.

 

$ Debt = 
LASTNONBLANKVALUE(
    Dim_Date[Date], // use the row label field of matrix.

    VAR Live = CALCULATE (SUMX(FILTER(Fact table, report = "Live" && {conditions}), debt_amt), userelationship(timestamp, date))

    Var Historical = CALCULATE (SUMX(FILTER(Fact table, report = "Historical" && {conditions}), debt_amt)),userelationship(timestamp, date))

    RETURN 

    IF (YEAR(Max(Dim_Date[Date])) = YEAR(TODAY) && MONTH(MAX(Dim_Date[Date])) = MONTH(TODAY), Live, Historical)
)

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~

 

 

View solution in original post

1 REPLY 1
xifeng_L
Solution Sage
Solution Sage

Hi @13havya ,

 

You can try below measure.

 

$ Debt = 
LASTNONBLANKVALUE(
    Dim_Date[Date], // use the row label field of matrix.

    VAR Live = CALCULATE (SUMX(FILTER(Fact table, report = "Live" && {conditions}), debt_amt), userelationship(timestamp, date))

    Var Historical = CALCULATE (SUMX(FILTER(Fact table, report = "Historical" && {conditions}), debt_amt)),userelationship(timestamp, date))

    RETURN 

    IF (YEAR(Max(Dim_Date[Date])) = YEAR(TODAY) && MONTH(MAX(Dim_Date[Date])) = MONTH(TODAY), Live, Historical)
)

 

 

Did I answer your question? If yes, pls mark my post as a solution and appreciate your Kudos !

 

Thank you~

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.