Forum Discussion

Khomotjo's avatar
Khomotjo
Helper II
1 year ago
Solved

Cumulative Measure Returning Errors

Hello Everyone,

 

I am trying to calculate the  total invoiced MTD for the month of April. 

I have used a variable to return the month I want to look at. For some reason this returns  March and April. Howver if I use a no( i.e 4 for April) I get the correct months data returned.

 

Anyone has an idea where I could be going wrong ?

 

Cumulative Invoiced =

 Var period = MONTH(MAX(Movement[EntryDate1]))

RETURN
CALCULATE(
    [Invoiced],
    FILTER(
         ALLSELECTED(Movement[Date]),
     Movement[Date]<=MAX(Movement[Date])&& MONTH(Movement[Date])= period)
    )
Need Help
  • shafiz_p's avatar
    shafiz_p
    1 year ago

    Hi Khomotjo ,

     

    Try this :

    Cumulative Invoiced =
VAR period = MONTH(MAX(Movement[EntryDate1]))
RETURN
CALCULATE(
    [Invoiced],
    FILTER(
        ALL(Movement[Date]),
        Movement[Date] <= MAX(Movement[Date]) && MONTH(Movement[Date]) = period
    )
)

     

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution and a kudos!!

     

    Best Regards,
    Shahariar Hafiz

3 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Khomotjo 

     

    Why not use DATESMTD or a time intelligence function but instead use FILTER? Also, in your formula, why reference different date columns - period variable uses EntryDate1?

     

    Other than this, please provide a workable sample data (not an image), your expected result from the same sample data and your reasoning behind. You may post a link to Excel or a sanitized copy of your PBIX stored in the cloud.

  • shafiz_p's avatar
    shafiz_p
    Super User
    1 year ago

    Hi Khomotjo ,

     

    Try this :

    Cumulative Invoiced =
VAR period = MONTH(MAX(Movement[EntryDate1]))
RETURN
CALCULATE(
    [Invoiced],
    FILTER(
        ALL(Movement[Date]),
        Movement[Date] <= MAX(Movement[Date]) && MONTH(Movement[Date]) = period
    )
)

     

     

    Hope this helps!!

    If this solved your problem, please accept it as a solution and a kudos!!

     

    Best Regards,
    Shahariar Hafiz

  • v-nmadadi-msft's avatar
    v-nmadadi-msft
    Community Support
    1 year ago

    Hi Khomotjo,

    May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

    Thank you.