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 having difficulty with the 'YTD' column:

 

 

 

Can anybody enlighten me? It's driving me mad. I have a good calendar table with dates in the format dd-mm-yyyy and also periods in the format yyyymm.

 

Thanks!

 

 

 

 

 

 

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

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Try YTD = TOTALYTD([Period],CalendarTable[Date])

    Where [Period] is your usual "SUM" measure

     

    • jrobinson's avatar
      jrobinson
      Advocate IV
      9 years ago

      This gives me the same values in my YTD measure as in my Period measure when I make the matrix table, with Period in columns, Company Name in rows, and Sales (Period) and Sales (YTD) (measures) in Values

      • 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