cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jdalfonso
Helper I
Helper I

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, why is getting the blank value? The card visual should be getting $3.444,386 as the previous month value

2. Why did previous quarter price sum? I only need to pick last quarter value because my end goal is to create a QoQ moving average

 

Formula Used:

 

Previous Month: 

Previous Month Price = CALCULATE(sum('b2b_datalab_cvm_pp_result JJ_Mobility_Trading_Ranges'[ARPU]), PREVIOUSMONTH('b2b_datalab_cvm_pp_result JJ_Mobility_Trading_Ranges'[Renewal Date YM]))
 
Previous Quarter:
Previous Quarter Price = CALCULATE(sum('b2b_datalab_cvm_pp_result JJ_Mobility_Trading_Ranges'[ARPU]), PREVIOUSQUARTER('b2b_datalab_cvm_pp_result JJ_Mobility_Trading_Ranges'[Renewal Date YM]))

jdalfonso_0-1621393578000.png

 

Note: I cannot use dateadd function in the 1st question because I have a slicer for the renewal date. I need a dynamic measure for the previous month price depending on the filter for renewal date: 

jdalfonso_1-1621393743581.png

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@jdalfonso , 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])))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

3 REPLIES 3
amitchandak
Super User
Super User

@jdalfonso , 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])))

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

View solution in original post

Hi,

 

Thanks for the reply.

 

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

 

What I did was calendarauto

@jdalfonso , Calender  or calendar Auto

 

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

 

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.

 

 

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!! !!
Dashboard of My Blogs !! Connect on Linkedin

Learn Power BI Beginners !! Advance Power BI Concepts !! Power BI For Tableau User !! Learn Power BI in Hindi !!
Proud to be a Super User!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!