Forum Discussion

darylmc's avatar
darylmc
Frequent Visitor
6 years ago

Report Builder - % LY Measure

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

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    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's avatar
      darylmc
      Frequent Visitor
      6 years ago

      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's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

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