Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
The following formulae are displaying each month's respective figure rather than a running total.
"YTD Revenue = CALCULATE(SUM(Table1[Revenue]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])))"
"LY YTD = CALCULATE([YTD Revenue], SAMEPERIODLASTYEAR('Calendar'[Date]))"
To calculate YoY difference and variance I am using the following:
Difference = [YTD Revenue]-[LY YTD]
Variance % = DIVIDE([Difference],[LY YTD])
A waterfall chart is being used to display the difference, but with above formula I am only getting total revenue of FY 2018.
The formula for variance is displaying previous months' headings with no value.
Hi @Atif,
Do you want to use a Waterfall chart to display YOY difference or running total? Could please share some sample data and show us the current result you have gotten?
Regards,
Yuliana Gu
The waterfall chart is not a must. I am interested in:
The option of having a measure each for "YTD Total", "LY YTD", "Difference" and "Variance" will save me from creating 3 separate measures for each year i.e., "FY1
A sample of my data is as under with a lot of hidden columns.
Day Revenue Station BU Sub Category Paper Size Stations Date
Weekday | 5,100 | Lahore | DJL | Back Page Panel | Jang | B (6-10) | L | Dec/1/2011 |
Weekday | 3,825 | Lahore | DJL | ROP - Announcement | Jang | B (6-10) | L | Dec/1/2011 |
Sunday | 3,953 | Lahore | DJL | ROP - Small Size | Jang | A( 1-5) | L | Nov/1/2011 |
Weekday | 2,550 | Multan | DJM | ROP - Small Size | Jang | B (6-10) | M | Jul/1/2011 |
Weekday | 2,550 | Multan | DJM | ROP - Small Size | Jang | B (6-10) | M | Aug/1/2011 |
Weekday | 3,366 | Rawalpindi | DJR | ROP - Small Size | Jang | B (6-10) | R | Jul/1/2011 |
Sunday | 5,387 | Karachi | TNK | ROP - Small Size | News | B (6-10) | K | Jul/1/2011 |
Sunday | 76,886 | Rawalpindi | DJR | ROP - Prime Display | Jang | G (Other) | R | Jul/1/2011 |
Sunday | 10,816 | Rawalpindi | DJR | ROP - Small Size | Jang | B (6-10) | LR | Jul/1/2011 |
Sunday | 12,996 | Rawalpindi | DJR | ROP - Small Size | Jang | B (6-10) | R | Jul/1/2011 |
Sunday | 77,976 | Rawalpindi | DJR | ROP - Small Size | Jang | B (6-10) | R | Jul/1/2011 |
Weekday | 22,572 | Rawalpindi | DJR | ROP - Small Size | Jang | B (6-10) | R | Jul/1/2011 |
Sunday | 3,249 | Rawalpindi | DJR | ROP - Small Size | Jang | A( 1-5) | R | Jul/1/2011 |
Sunday | 10,816 | Rawalpindi | DJR | ROP - Small Size | Jang | B (6-10) | LR | Jul/1/2011 |
Sunday |
The data against "Difference" is something like this:
Difference | Fiscal Year | Comment |
xxxxxxxxxx | FY18 | The exact amount is being displayed for current fiscal year |
0 | FY12 | |
0 | FY13 | |
0 | FY14 | |
0 | FY15 | |
0 | FY16 | |
0 | FY17 |
The data against "Variance" is bringing no value
Variance % | Fiscal Year |
0 | FY12 |
0 | FY13 |
0 | FY14 |
0 | FY15 |
0 | FY16 |
0 | FY17 |
Hi @Atif
Just to give an idea you can make use of quick measures it may help you out. refer the image below
Hi there @Anonymous
Thanks a bunch!
The quick measure "Running Total" is displaying respective month's figures without adding up in following months.
Moreover, I need running total based on Fiscal Year (July to June).
The result of quick measure "Year-to-date total" is blank report.
Hi @Atif
Rolling total working good for me can you check once again and if its possible can show some ref images
@Anonymous
To avoid any error being carry forwarded, I connected a fresh set of data (fictitious), yet nothing happened with "quick measures".
The result of the two measures "Year-to-date total" and "Running total" is as under.
Month Name | FY12 | FY13 | FY14 | FY15 | FY16 | FY17 | FY18 | Total |
July | 302 | 332 | 378 | 393 | 408 | 438 | 453 | 2,703 |
August | 302 | 332 | 378 | 393 | 408 | 438 | 453 | 2,703 |
September | 302 | 332 | 378 | 393 | 408 | 438 | 453 | 2,703 |
October | 302 | 332 | 378 | 393 | 408 | 438 | 453 | 2,703 |
November | 302 | 332 | 378 | 393 | 408 | 438 | 453 | 2,703 |
December | 302 | 332 | 378 | 393 | 408 | 438 | 453 | 2,703 |
January | 302 | 332 | 378 | 393 | 408 | 438 | - | 2,250 |
February | 302 | 332 | 378 | 393 | 408 | 438 | - | 2,250 |
March | 302 | 332 | 378 | 393 | 408 | 438 | - | 2,250 |
April | 302 | 332 | 378 | 393 | 408 | 438 | - | 2,250 |
May | 302 | 332 | 378 | 393 | 408 | 438 | - | 2,250 |
June | 302 | 332 | 378 | 393 | 408 | 438 | - | 2,250 |
Total | 3,624 | 3,986 | 4,530 | 4,711 | 4,892 | 5,255 | 2,718 | 29,717 |
The result of other three measures "Year-over-year change", "Month-over-month change" and "Percentage difference" is:
Month Name | FY12 | FY13 | FY14 | FY15 | FY16 | FY17 | FY18 | Total |
July | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
August | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
September | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
October | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
November | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
December | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
January | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
February | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
March | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
April | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
May | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
June | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
Total | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% | 0.00% |
Please note that I have tried to work with both "calendar year" and "fiscal year".
All I need now is a measure for:
- YTD Revenue;
- YTD Revenue for previous years;
- YoY Difference; and
- YoY Variance
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |