Forum Discussion
Helper IVQuarter Snapshot vs Culumlative Quarter Calculation
The values in my model are a snapshot in time, not a cumulative total. So for example, when I want to compare the values from the previous quarter, I want do compare to the value on a specific date, not the cumulative value for the quarter. I have been using the measure formula below but it is giving me a cumulative total for the quarter instead of a snapshot in time.
Value Qtr Prior = CALCULATE([Total Value], PREVIOUSQUARTER('Calendar'[Date]))
Example:
12/31/2019 | 20,000.00 |
1/31/2020 | 21,000.00 |
2/29/2020 | 22,000.00 |
3/31/2020 | 21,500.00 |
4/30/2020 | 22,000.00 |
In Jan 2020, I want the calculation to give me the value for Dec 2019 of 20,000
In Feb 2020, I also want it to give me the value for Dec 2019 of 20,000
In Mar 2020, I also want this measure to return the value for Dec 2019 of 20,000
In April 2020, I would like it to give me the value for March 2020 of 21,500
This formula has been giving me the sum for the previous quarter so instead of 21,500 for the previous quarter in April, it is giving me the sum of Jan+Feb+Mar which is 63,000. I don’t want the running total, I want the snapshot value for the previous quarter.
I'm trying to move on to the last date on the last qtr in both formulas
CALCULATE([Total Value], lastdate(PREVIOUSQUARTER('Calendar'[Date])))
3 Replies
- amitchandak
Super User
ARob198 ,Try Like
Last QTD Sales = CALCULATE([Total Value],lastdate(DATESQTD(dateadd('Calendar'[Date],-1,QUARTER))))
CALCULATE([Total Value], lastdatePREVIOUSQUARTER('Calendar'[Date])))
ARob198Hi,
Thanks for the quick response. That seems to work great to give me a rolling quarter but it doesn't give me the previous quarter only. So for March, it gives me Dec. For April, it gives me Jan. For May, it gives me Feb. These are rolling quarters.
I would like a formula that gives me the following.
March returns December.
April returns March.
May returns March.
June returns March.
July returns June.
Aug returns June.
Sept returns June.
Oct returns Sept.
I want a static quarter end number to compare to. Hopefully I am explaining it clearly. Or perhaps I am not understanding how to use the second part of the formula you have below: CALCULATE([Total Value], lastdatePREVIOUSQUARTER('Calendar'[Date])))
Thank you!
- amitchandak
I'm trying to move on to the last date on the last qtr in both formulas
CALCULATE([Total Value], lastdate(PREVIOUSQUARTER('Calendar'[Date])))