Forum Discussion

netanel's avatar
netanel
Post Prodigy
3 years ago
Solved

< 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 { "Fi...
Need Help
  • pi_eye's avatar
    pi_eye
    3 years ago

    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

pi_eye's avatar
pi_eye
Resolver IV
3 years ago

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