Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Help needed ASAP PLEASE - Previous Month Function and Previous Quarter not working accurately for me

Hi,   Goal: Get previous month value and put it into a card visual. I also need to get previous quartervalues because I will make a QoQ moving average later on.   Questions: 1. From below image,...
Tips and Tricks
  • amitchandak's avatar
    amitchandak
    5 years ago

    Anonymous , I think PREVIOUSQUARTER, PREVIOUSMONTH take the first date. so the basis are 01/05/2020, for both. Before it. If you need based on 30/04/2021 you need use datesqtd, datesmtd.

     

    Also use date table, not the date column from your table

     

    example

    QTD Sales = CALCULATE(sum('b2b_datalab_cvm_pp_result JJ_Mobility_Trading_Ranges'[ARPU]),DATESQTD(('Date'[Date])))

    Last QTD Sales = CALCULATE(sum('b2b_datalab_cvm_pp_result JJ_Mobility_Trading_Ranges'[ARPU]),DATESQTD(dateadd('Date'[Date],-1,QUARTER)))


    Qtr Sales = CALCULATE(sum('b2b_datalab_cvm_pp_result JJ_Mobility_Trading_Ranges'[ARPU]),DATESQTD(ENDOFQUARTER('Date'[Date])))

     

     

    Last QUARTER Sales = CALCULATE(sum('b2b_datalab_cvm_pp_result JJ_Mobility_Trading_Ranges'[ARPU]),DATESQTD( ENDOFQUARTER(dateadd('Date'[Date],-1,QUARTER))))

     

     

    MTD Sales = CALCULATE(sum('b2b_datalab_cvm_pp_result JJ_Mobility_Trading_Ranges'[ARPU]),DATESMTD('Date'[Date]))

     

    last MTD Sales = CALCULATE(sum('b2b_datalab_cvm_pp_result JJ_Mobility_Trading_Ranges'[ARPU]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))

     

    this month = CALCULATE(sum('b2b_datalab_cvm_pp_result JJ_Mobility_Trading_Ranges'[ARPU]),DATESMTD(ENDOFMONTH('Date'[Date])))

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi,

 

Thanks for the reply.

 

How do you do the Date'[Date]'? 

 

What I did was calendarauto

amitchandak's avatar
amitchandak
Super User
5 years ago

Anonymous , Calender  or calendar Auto

 

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

 

various other link to create a calendar

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.