Report Builder - % LY Measure

[darylmc]

Hi All,

apologies if this is not the right forum.

I'm attempting to create the equivalent of the Measure within Power BI Report Builder but I'm stumped.

It would be something like Sum(Sales TY)/Sum(Sales LY) - 1 but Report Builder doesn't allow aggregates within calculated fields.

Any help appreciated

[amitchandak]

Make sure you have created powerbi date calendar and joined it with your fact

Date calendar
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions

time formula's you can create

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date Filer]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date Filer],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date Filer],-1,MONTH))))

QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date Filer])))

Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date Filer],-1,QUARTER)))
Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date Filer],1,QUARTER)))

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date Filer],-1,Year)))

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date Filer])))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date Filer],-1,Year)))


Change % YOY = divide([YTD Sales],[Last YTD Sales]) -1

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks.

My Recent Blog - https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601

[darylmc]

Thanks amitchandak 

The time part is not an issue as I have a Sales LY within the same row. Issue is that in Report Builder, it doesn't seem to allow aggregates which means I cannot get % LY into a matrix.

[amitchandak]

To help you further I need pbix file. If possible please share a sample pbix file after removing sensitive information.