Forum Discussion

ToffeeMark's avatar
ToffeeMark
Frequent Visitor
6 years ago
Solved

Sales Growth SAMEPERIODLASTYEAR Help

Hi,

 

I'm fairly new to Power BI, I've gone through the Microsoft Power BI training videos but lack experience so I hope someone could help with my question.

 

I've made two measures that work to some extent, one references the Date field in the Sales file, one  reference the date field in the Date table.

 

Note earlier measure = 
Sales Value £ YTD = TOTALYTD(SUM(''My Sales Data Table'[Value £]),'My Sales Data Table'[Date])

 

The first 1 accurately gives me last years sales (e.g. Jan - Aug)

LY YTD £ = CALCULATE([Sales Value £ YTD],(SAMEPERIODLASTYEAR('My Sales Data Table'[Date])))

 
The second gives me the whole of last years sales (Jan-Dec) so isn't YTD (all of 2018)
LY YTD £ (2) = CALCULATE([Sales Value £ YTD],(SAMEPERIODLASTYEAR('Date Table'[Date])))

 

I like that the first measure works, however if I try to filter a matrix by Product the visualisation breaks (presumably because there are dates missing? The 2nd measure doesn't break which suggests I should be using the Date table but then it's giving the whole of 2018?)

 

Does anyone know where I'm going wrong?

 

Thanks

Mark

 

 

 

 

Need Help
Tips and Tricks
  • v-juanli-msft's avatar
    v-juanli-msft
    6 years ago

    Hi ToffeeMark 

    Create measures

    sales current = SUM(Sheet2[sales])

max date = CALCULATE(MAX(Sheet2[date]),ALLSELECTED(Sheet2))

end of the month = EOMONTH([max date],0)

ytd current = TOTALYTD([sales current],'calendar'[Date])

ytd current 2 = CALCULATE([ytd current],FILTER('calendar','calendar'[Date]<=[end of the month]))

     

    ytd last year 1 = CALCULATE([ytd current 2],SAMEPERIODLASTYEAR('calendar'[Date]))

ytd last year 2 = IF(MONTH(MAX('calendar'[Date]))<=MONTH([max date]),[ytd last year 1])

ytd last year 3 = MAXX(FILTER('calendar','calendar'[month]<=MONTH([max date])),[ytd last year 2])

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

2 Replies

  • calerof's avatar
    calerof
    Impactful Individual
    6 years ago

    Hi ToffeeMark ,

     

    Try modifying your first measure Total Sales (note earlier measure) as follows:

     

     

    Total Sales = SUM( My Sales Data Table[Value Lb] )

     

    or, you could use:

    Sales YTD = CALCULATE(
                [Total Sales],
                DATESYTD( 'Calendar'[Date] )
            )

     

    Regards,

     

    Fernando 

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    6 years ago

    Hi ToffeeMark 

    Create measures

    sales current = SUM(Sheet2[sales])

max date = CALCULATE(MAX(Sheet2[date]),ALLSELECTED(Sheet2))

end of the month = EOMONTH([max date],0)

ytd current = TOTALYTD([sales current],'calendar'[Date])

ytd current 2 = CALCULATE([ytd current],FILTER('calendar','calendar'[Date]<=[end of the month]))

     

    ytd last year 1 = CALCULATE([ytd current 2],SAMEPERIODLASTYEAR('calendar'[Date]))

ytd last year 2 = IF(MONTH(MAX('calendar'[Date]))<=MONTH([max date]),[ytd last year 1])

ytd last year 3 = MAXX(FILTER('calendar','calendar'[month]<=MONTH([max date])),[ytd last year 2])

    Best Regards
    Maggie
    Community Support Team _ Maggie Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.