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

1. Make sure you put a Period column from your Calendar table on columns 

2. Make sure you defined a relationship between Calendar Table and Fact table

3. If this doesn't work - send me a link to your .pbix file I'll take a look

Michael

jrobinson's avatar
jrobinson
Advocate IV
9 years ago

I haven't had to use the calendar table in the end. I have a date column in my Sales table which aligns to the period i.e. 201601 = 01-01-2016. SO I don't actually use the calendar table at all. Only Sales (for prd), Sales (YTD), Date and CompanyName. When I put Date on columns it only shows me the years, nothing more granular

  • 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!!