Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jesse_james
Helper I
Helper I

DATESYTD with IF condition on date

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)

 

DAX_datesytd.PNG

2 REPLIES 2
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI 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 2testing line 4
2020April506953.07506953.07
2020May1169404.14662451.07
2020June1797936.48628532.34
2020July671934.85671934.85
2020August1190287.93518353.08
2020September1919799.33729511.4
2020October2592838.54673039.21
2020November3584367.95991529.41
2020December5091671.461507303.51

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors