Hi Expert
I am trying to get the current quarter vs Previous Year Quarter numbers in Power BI. It is working fine for all the quarters, but the problem is with the total of last year vs a total of the current year.
In my calculation, we have only a quarter-time period in the date table. Below is the formula used.
I have created a quarter count column and based on the quarter count minus -4 in the calculate function.
CALCULATE([Revenue],FILTER(ALL(FiscalQuarter), FiscalQuarter[CountQtr]=MAX(FiscalQuarter[CountQtr])-4))
Please help how to calculate the FY total no. of current year vs. Previous Year.
|
FY2019 |
FY2019 |
FY2019 |
FY2019 |
|
FY2020 |
FY2020 |
FY2020 |
FY2020 |
|
|
|
Q1 |
Q2 |
Q3 |
Q4 |
FY |
Q1 |
Q2 |
Q3 |
Q4 |
FY |
|
| TCV |
0 |
0 |
0 |
0 |
0 |
12.5 |
8.79 |
|
|
|
|
| HWU |
237.2180814 |
241.3017869 |
250.0932499 |
258.8402344 |
987.45 |
219.0018922 |
159.231719 |
|
|
|
|
| HWR |
191.8 |
196.1 |
203.04 |
194.37 |
785.31 |
182.54 |
126.59 |
|
|
|
|
| SLR |
5.79 |
6.24 |
6.33 |
6.62 |
24.98 |
5.79 |
5.67 |
|
|
|
|
| SVR |
3.85 |
4.32 |
4.29 |
4.27 |
16.73 |
4.19 |
3.98 |
|
|
|
|
| Revenue |
201.43 |
206.66 |
213.65 |
205.26 |
827 |
192.52 |
136.24 |
|
|
|
|
| RevenuePYPQ |
|
|
|
|
|
201.43 |
206.66 |
213.65 |
205.26 |
205.26 |
827 |
| RevenuePYPQ% |
|
|
|
|
|
|
|
|
|
|
|
HI @putturps
If i am not wrong you are trying to get this year vs last year from your qtr formula?
Also, Can you please advice on what you want to achieve as an end result? Also, if you can upload a sample data or a pbix on which you have worked i may be able to help.
Regards,
Hemant
Hi Hemantsing,
Thanks for reaching quickly.
I am looking below two measures to calculate as per last year quarter.
RevenuePYPQ
RevenuePYPQ %
In RevenuePYPQ formula it should display the FY2019 (827) in FY2020 (Column as 827) but it currently taking 205.06.
Since my formula I used max of quarter and its taking the Max(quarter of FY2019-Q4 values in the FY total (205.06) instead of 827.
CALCULATE([Revenue],FILTER(ALL(FiscalQuarter), FiscalQuarter[CountQtr]=MAX(FiscalQuarter[CountQtr])-4))
Please help how to achieve this number.
Hey @putturps
Since I do not have an active sample dataset using which i can test my approach and understand your model layouts but i believe you can take help of selected value function.
Like This…
RevenuePYPQ = switch(True(),
selectedvalue(Tablename[Column That contains FY text])=“FY”,[RevenuePYPQ],
[RevenuePYPQ]
)
All you need to do is to tell the RevenuePYPQ to return a result for “FY”. Hope this gives you some idea to move forward.
Regards,
Hemant
Thank you, but didn’t worked as i expected.
Blue ones are showing numbers are fine, but RED ones are the issue.
I am attaching the sample .pbix file please check and help.Text Scenario.pbix (89.9 KB)
Try using variables.
You will need to get the dates in previous quarter using variable before the return to give the output because variable will get evaluated first.
Thanks Williamnt,
I was able to solve this problem using variables.
RevenuePYPQ 1 =
VAR FY = CALCULATE([RevenueM],FILTER(ALL(FiscalQuarter), FiscalQuarter[CountYear]=MAX(FiscalQuarter[CountYear])-1))
VAR QTR = CALCULATE([RevenueM],FILTER(ALL(FiscalQuarter), FiscalQuarter[CountQtr]=MAX(FiscalQuarter[CountQtr])-4))
RETURN
IF(HASONEVALUE(FiscalQuarter[FiscalQuarter]), QTR, FY)
1 Like
Glad to help. Have a good day!
