Year to Date split by Period

[jrobinson]

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!

[Anonymous]

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

Where [Period] is your usual "SUM" measure

[jrobinson]

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]

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]

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 · Accepted solution]

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]

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