Forum Discussion

jrobinson's avatar
jrobinson
Advocate IV
9 years ago
Solved

Year to Date split by Period

Hi,   I need to create a measure in Power BI which calculates Sales Value for the Year to Date but split out by period. I've created the table I'm aiming for in Excel for clarification, and I'm hav...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    OK. That's why TOTALYTD is not working.

    Try this then:

    CALCULATE([Period],FILTER(ALL(Fact),Fact[Date]<=MAX(Fact[Date]) && Fact[Year]=MAX(Fact[Year]))

    Michael

Anonymous's avatar
Anonymous
Not applicable
9 years ago

OK. That's why TOTALYTD is not working.

Try this then:

CALCULATE([Period],FILTER(ALL(Fact),Fact[Date]<=MAX(Fact[Date]) && Fact[Year]=MAX(Fact[Year]))

Michael

jrobinson's avatar
jrobinson
Advocate IV
9 years ago

Thanks Anonymous! Your formula largely gave me what I was looking for. In the end I've used the following formula to allow me to split out by company:

 

CALCULATE([Period],FILTER(ALLEXCEPT(Fact, Fact[CompanyName]),Fact[Date]<=MAX(Fact[Date ]) && Fact[Year]=MAX(Fact[Year])))

 

Thanks for your help!!