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
ValeriaBreve
Post Patron
Post Patron

help with a formula to filter the sum of a calculation

Hello,

I am trying to perform what is seemingly an easy calculation but I cannot get there in DAX.

I am reading several forecats files, to which I give a version: "n" for this month, "n-1" for last month, etc.

I also have an actual spending table, with spending per month up to the latest month of available data (so no versioning). 

What I need to do it to be able to sum up the actuals but only up to the date of the version selected (or version used as a legend in a chart is what I would like to achieve, to show the different curves of forecast + actual per month by file version).

 

I have a table with file versions that I am reading, that I have now included in my date table (which I hope is the correct way of doing thigs):

 

ValeriaBreve_0-1685115694511.png

 

The Actuals table just has 4 columns: Project Number, Date, Spending and Year

 

then I have tried to created a measure for summing the actuals, but it is not working 😞

 

Spending Versioned (US$) =
VAR VersionSelected = (SELECTEDVALUE(DateTable[EOM Date]))
VAR ActualsAmount = CALCULATE(SUM('ACTUALS'[Spending USH$]),
FILTER(
    DateTable,DateTable[Date]<VersionSelected)
)
RETURN
ActualsAmount

 

Can you please guide me in how to do this properly?

 

Thanks!

Kind regards

Valeria

 

1 REPLY 1
some_bih
Super User
Super User

Hi @ValeriaBreve  check bracket at start and at end in part VAR VersionSelected = (SELECTEDVALUE(DateTable[EOM Date])). I hope this help.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






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.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.