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.
Hi,
I need to create a DAX formula with running total for fiscal year but only for dates after March 2020- to have projection.
I have created measure TESTING LINE 2 but running total include all date since July even if this dates are blank.
I would like to start running total in April 2020 but with value 506953.07. Any help?
My DAX below:
TESTING LINE 4 =
CALCULATE (SUM ( 'Facts'[Consumption] ), SAMEPERIODLASTYEAR( '1 Dim_Calendar'[Date] ))
TESTING LINE 4 =
CALCULATE (SUM ( 'Facts'[Consumption] ), SAMEPERIODLASTYEAR( '1 Dim_Calendar'[Date] ))
TESTING LINE 2 = IF(MIN('1 Dim_Calendar'[Date]) <= [Historic Data Last Date],BLANK(),CALCULATE([TESTING LINE 4],DATESYTD(('1 Dim_Calendar'[Date]),"30/06")))
TESTING LINE 2 = IF(MIN('1 Dim_Calendar'[Date]) <= [Historic Data Last Date],BLANK(),CALCULATE([TESTING LINE 4],DATESYTD(('1 Dim_Calendar'[Date]),"30/06")))
[Historic Data Last Date]=MAX('Facts'[Date])
[Historic Data Last Date]=MAX('Facts'[Date]) (has value: 1/03/2020)
I am not clear on the IF() part of what you are asking but would this approach work for your running total from April
NewMeasure = var maxdate = MAX('1 Dim_Calendar'[Date])
Return CALCULATE (SUM ( 'Facts'[Consumption] ), DATESBETWEEN('1 Dim_Calendar'[Date], DATE(2020,4,1), maxdate))
If this works for you, please mark it as solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Hi Pat,
thanks for you answer.
DATESBETWEEN would be fine but I need to start calculating next fiscal year in my running total from Jul 2020.
So:
from April- Jun 2020 as running total for FY2019
from Jul 2020-Jun2021 as running total in new fiscal year
like here:
testing line 2 | testing line 4 | ||
2020 | April | 506953.07 | 506953.07 |
2020 | May | 1169404.14 | 662451.07 |
2020 | June | 1797936.48 | 628532.34 |
2020 | July | 671934.85 | 671934.85 |
2020 | August | 1190287.93 | 518353.08 |
2020 | September | 1919799.33 | 729511.4 |
2020 | October | 2592838.54 | 673039.21 |
2020 | November | 3584367.95 | 991529.41 |
2020 | December | 5091671.46 | 1507303.51 |
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 |
---|---|
37 | |
25 | |
19 | |
14 | |
8 |
User | Count |
---|---|
69 | |
48 | |
44 | |
19 | |
15 |