Forum Discussion

ArchStanton's avatar
ArchStanton
Icon for Power Participant rankPower Participant
2 years ago

Previous YTD Measure not working

Hi,

The following code works perfectly right up to Dec and then it incorrectly resets in January - I want it to continue until Mar 31st which is the end of our Financial Year using the numbers in Blue from the 12mth Cumulative Total measure.

 

Cases Previous YTD = 
VAR PreviousYr = SAMEPERIODLASTYEAR(Date2[Date])
VAR PreviousYTD = DATESYTD(PreviousYr)
RETURN
CALCULATE([Active Cases],
    PreviousYTD,
    REMOVEFILTERS('Cases')
)

 

 

The image below shows the incorrect numbers in Red and the blue circled numbers are what should be in place of the 93, 195, 312.

 

Can anyone see why the breaks down after the Calendar Year ends?

 

I'll add the code of YTD Active Cases and 12mth Cumulative Total just in case that helps.

Ps If i remove the REMOVEFILTERS function then I don't get any data at all.

 

YTD Active Cases Measure:

 

YTD Active Cases = CALCULATE(
    TOTALYTD(COUNT('Cases'[Case Number]),'Cases'[Created On],"31/3"),
    'Cases'[statecode] = "Active")

 

 

 12mth Cumulative Total Measure:

 

12mth Cumulative Total = /* always calculates 12mths no matter what month*/
CALCULATE(
    [YTD Active Cases],
    DATESINPERIOD(
        Date2[Date],
        MAX(Date2[Date]),
        -12,
        MONTH),
        REMOVEFILTERS('Cases'))

 

 

Thanks

Need Help
Show and Tell

6 Replies

  • amustafa's avatar
    amustafa
    Icon for Solution Sage rankSolution Sage
    2 years ago

    Try this updated DAX according to your speific fiscal year.

     

    Cases Previous YTD =
    VAR CurrentFYStart = DATE(YEAR(Date2[Date]) - (MONTH(Date2[Date]) < 4 ? 1 : 0), 4, 1)
    VAR PreviousFYStart = DATE(YEAR(CurrentFYStart) - 1, 4, 1)
    VAR PreviousFYEnd = DATE(YEAR(CurrentFYStart), 3, 31)
    VAR PreviousYTD = DATESBETWEEN(Date2[Date], PreviousFYStart, PreviousFYEnd)

    RETURN
    CALCULATE([Active Cases],
    PreviousYTD,
    REMOVEFILTERS('Cases')
    )

     

    • ArchStanton's avatar
      ArchStanton
      Icon for Power Participant rankPower Participant
      2 years ago

      HI,

      Thanks for your reply.

       

      I'm getting the following error message:

      I had to modify the first Variable because DATE does not accept 'Date2'[Date] Calender as an argument

       

       

       

  • Gayatri_D05's avatar
    Gayatri_D05
    Icon for Resolver II rankResolver II
    2 years ago

    Hi ArchStanton ,
    I tried your formulas on a dummy data and noticed few changes required in your cases previous YTD measure:

    Cases Previous YTD = 
CALCULATE([Active Cases],
    DATEADD ('Date'[Date], -12, MONTH ),"31/3")
    REMOVEFILTERS('Cases')
)

    Please try the above measure and let me know if it works for you.


    I hope I was able to resolve your issue. If yes please mark it so it can help others as well. Thanks ğŸ˜Š




    • ArchStanton's avatar
      ArchStanton
      Icon for Power Participant rankPower Participant
      2 years ago

      Hi, I get the error message "The True/False expression does not specify a column" when I use your code.

      Cases Previous YTD2 = 
    CALCULATE([Active Cases],
    DATEADD ('Date2'[Date], -12, MONTH ),"31/3",
    REMOVEFILTERS('Cases')
)
      • Gayatri_D05's avatar
        Gayatri_D05
        Icon for Resolver II rankResolver II
        2 years ago

        Hi,

        CALCULATE(
    TOTALYTD(COUNT('Cases'[Case Number]),DATEADD ('Date2'[Date], -12, MONTH ),"31/3",
    'Cases'[statecode] = "Active"))

         Can you try this ? I have just replaced the active case in the measure with its actual measure.