Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Last month each year calculation

Hi,

 

I have a data set with lets say Sales Rate. This rate is different each month. I want to build a bar chart where the sales rate for the last month, Dec is shown for each year. And for this year the last month of data is shown, Sept in our case. 

2019=.89

2018= .83

2017=.80

2016=.80

 

MonthName-YearPlacementRate
Sep-190.89%
Sep-180.92%
Sep-170.94%
Sep-160.83%
Oct-180.90%
Oct-170.92%
Oct-160.85%
Nov-180.85%
Nov-170.85%
Nov-160.83%
May-190.87%
May-180.88%
May-170.87%
May-160.80%
Mar-190.88%
Mar-180.89%
Mar-170.00%
Mar-160.78%
Jun-190.87%
Jun-180.89%
Jun-170.90%
Jun-160.82%
Jul-190.88%
Jul-180.90%
Jul-170.92%
Jul-160.84%
Jan-190.44%
Jan-180.36%
Jan-170.00%
Jan-160.80%
Feb-190.48%
Feb-180.41%
Feb-170.00%
Feb-160.77%
Dec-180.83%
Dec-170.80%
Dec-160.80%
Aug-190.89%
Aug-180.90%
Aug-170.93%
Aug-160.88%
Apr-190.86%
Apr-180.88%
Apr-170.85%
Apr-160.80%
Need Help

7 Replies

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

    Try like

     

    CALCULATE(sum(Sales[Sales Amount]),filter(sales,(year(Sales[Sales Date])<year(TODAY())&& MONTH(Sales[Sales Date])=12) || (year(Sales[Sales Date])=year(TODAY()) && MONTH(Sales[Sales Date])=MONTH(TODAY()) )))

    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

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

      Also can control date from fact

       

      Last month data = 
var _last_month =MONTH(maxx(all(Sales),Sales[Sales Date]))
return
CALCULATE(sum(Sales[Sales Amount]),filter(sales,(year(Sales[Sales Date])<year(TODAY())&& MONTH(Sales[Sales Date])=12) || (year(Sales[Sales Date])=year(TODAY()) && MONTH(Sales[Sales Date])=_last_month )))

       

      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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      amitchandak 

      Thanks ! 

       

      I tried this and I'm getting 0's with the formula below: 

       

      Placement2 = CALCULATE(([PlacementRate]),FILTER(DIM_Date,(YEAR(DIM_Date[MonthName-Year])<YEAR(TODAY()) && MONTH(DIM_Date[MonthName-Year])=12 || (YEAR(DIM_Date[MonthName-Year])=YEAR((TODAY()) && MONTH(DIM_Date[MonthName-Year])=MONTH((TODAY()) ))))))
      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        Is MonthName-Year is a date? My formula is based on date column

  • Icey's avatar
    Icey
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

    Is this problem solved?

     

    Best Regards,

    Icey

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      No I haven't solved it yet, the MonthYear is a date field that I've converted in PowerBI 

      • Icey's avatar
        Icey
        Icon for Community Support rankCommunity Support
        6 years ago

        Hi Anonymous ,

        If you don't mind, please share me a dummy PBIX file without real data and sensitive information.

         

        Best Regards,

        Icey