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
netanel
Post Prodigy
Post Prodigy

< Today

Hello everyone,
I am trying to run this formula,

OEP_Actual HC =
CALCULATE([Sum Amount N],
    'EpmScenario'[ScenarioLvl3] IN { "OEP_Actual" },
    'EpmVersion'[VersionLvl99Desc] IN { "Final" }
)


in two different scenarios
1. Only up to today's date
2. Only up to a month before

 

Thanks








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

Connect on Linkedin
linkedin.com/in/netanel-shriki
1 ACCEPTED SOLUTION
pi_eye
Resolver IV
Resolver IV

Hi @netanel 

 

I managed to get something working (with my own dataset) with this:

 

new measure = 
var currmonth= DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))
return calculate(sum(invoice[INVOICE QUANTITY]),invoice[DEALER] in {"A"}, invoice[DATE]<= currmonth)
 
The issue with the other solution that is failing below is that Month(Today()) returns an integer (eg "12") and you cannot filter by this as it is trying to compare with a date.
 
Your solution should look something like this:
 
OEP_Actual HC =
var currmonth= DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))
Return CALCULATE([Sum Amount N],
    'EpmScenario'[ScenarioLvl3] IN { "OEP_Actual" },
    'EpmVersion'[VersionLvl99Desc] IN { "Final" },
    Dim_Time[Date]<=currmonth
)

HTH

 

Pi

View solution in original post

5 REPLIES 5
pi_eye
Resolver IV
Resolver IV

Hi @netanel 

 

I managed to get something working (with my own dataset) with this:

 

new measure = 
var currmonth= DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))
return calculate(sum(invoice[INVOICE QUANTITY]),invoice[DEALER] in {"A"}, invoice[DATE]<= currmonth)
 
The issue with the other solution that is failing below is that Month(Today()) returns an integer (eg "12") and you cannot filter by this as it is trying to compare with a date.
 
Your solution should look something like this:
 
OEP_Actual HC =
var currmonth= DATE(YEAR(TODAY()),MONTH(TODAY())-1,DAY(TODAY()))
Return CALCULATE([Sum Amount N],
    'EpmScenario'[ScenarioLvl3] IN { "OEP_Actual" },
    'EpmVersion'[VersionLvl99Desc] IN { "Final" },
    Dim_Time[Date]<=currmonth
)

HTH

 

Pi

Manoj_Nair
Solution Supplier
Solution Supplier

@netanel - try this DAX solution.

 

Power BI DAX Solution 

Please don't hesitate to give kudos to useful posts and mark solution as accepted if this works for you.

 

If you are unable to crack this Pls message.

 

Many Thanks 

Connect me on LinkedIn

https://www.linkedin.com/in/manoj-nair-%E2%98%81-344666104

@Manoj_Nair 

 

 i try to run similar Measure but iam run in to problem

netanel_0-1670508299080.png

 








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

Connect on Linkedin
linkedin.com/in/netanel-shriki

@netanel try removing the extra parenthesis as mentioned by @v-yinliw-msft if it doesn't work, send me the sample datasets to check. Pls note my datasets is a simple one as shown below. 

image.jpg

Hi @netanel ,

 

Your formula has an extra parenthesis at the end, you can remove the parentheses from the last line to see if there is an error.

 

Hope these help you.

 

Best Regards,

Community Support Team _Yinliw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

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.