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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
don_mccrimmon
New Member

Prior Fiscal YTD Total

I want to create a report that calculates the current fiscal year to date quantity and compare that to the previous fiscal year total

 

so the current fiscal year would be July 1 2021 to Dec 6 2021

and the previous fiscal year would be July 1 2020 to Dec 6 2020.

 

I tried this formula

Prior Fiscal YTD 50 LB = CALCULATE(sum('DW Sales_Purchase_Fact'[Quantity_50LB]),'DW Time_DIM'[Fiscal_Year_Num]= FILTERS('DW Time_DIM'[Fiscal_Year_Num])-1 && 'DW Time_DIM'[Day_Fiscal_Year_Num] <= 159)

 

the above works fine but I don't want to hard code the 159 into the formula I want to calculate the number of days between July 1 and Today.  I have a calculation that calculates the number of days but the CALCULATE function doesn't seem to want to accept calculations.

 

1 ACCEPTED SOLUTION
don_mccrimmon
New Member

I did solve the problem one other way.  I created a second date column in my time table YTD_Date_DT and it is populated for all dates from July 1 (start of the fiscal year) until yesterday and then is null after that.  

I then summed my measure on that date 

CALCULATE( SUM('DW Sales_Purchase_Fact'[Quantity_50LB]), 'DW Time_DIM'[YTD_Date_DT] )

so as long as I can accurately keep this column in the time table up to date, it should work fine.

also allows the user to select 2, 3, 4, 5, 6 years to compare.

 

I just figured this out this morning, but thanks for all your help.  will try your solution as well.

View solution in original post

6 REPLIES 6
don_mccrimmon
New Member

I did solve the problem one other way.  I created a second date column in my time table YTD_Date_DT and it is populated for all dates from July 1 (start of the fiscal year) until yesterday and then is null after that.  

I then summed my measure on that date 

CALCULATE( SUM('DW Sales_Purchase_Fact'[Quantity_50LB]), 'DW Time_DIM'[YTD_Date_DT] )

so as long as I can accurately keep this column in the time table up to date, it should work fine.

also allows the user to select 2, 3, 4, 5, 6 years to compare.

 

I just figured this out this morning, but thanks for all your help.  will try your solution as well.

don_mccrimmon
New Member

this totals the whole year and not just the period from July 1st to Dec 6.

I want the total quantity for the same period each year.

the current year isn't a problem because it is an incomplete year but previous years are the issue

Sorry I missed what you were trying to achieve. There mght be an easier way to achieve it but I think this would work: (You need a financial year column in your date table)

Period Compare = 

VAR CurrentYear = 
    CALCULATE(
        MAX('Date'[Financial Year]),
        'Calendar'[Date] = TODAY()
    )

VAR LatestVisibleDate = LASTDATE('Date'[Date])

VAR MaxYearInFilterContext = 
    CALCULATE(
        MAX('Date'[Financial Year]),
        LatestVisibleDate
    )
        
VAR YearOffset = CurrentYear - MaxYearInFilterContext

VAR DatesThisYear = 
    CALCULATETABLE(
        DATESYTD('Calendar'[Date], "31/06"),
        'Calendar'[Date] <= TODAY()
    )

VAR PreviousYearDates = DATEADD(DatesThisYear, YearOffset, YEAR)

VAR Result =  
    CALCULATE(
        SUM('DW Sales_Purchase_Fact'[Quantity_50LB]),
        PreviousYearDates
    )

RETURN Result





Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

wow no wonder I didn't just figure this out on my own.

 

thanks

 

I'm hoping someone has a more elegant solution but it's something I've always had to fight to make work!



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!
bcdobbs
Super User
Super User

CALCULATE(
	SUM('DW Sales_Purchase_Fact'[Quantity_50LB]),
	DATESYTD(Date[Date], "31/12")
	)

 

Above should work for you, just replace "31/12" with the end of financial year date.

 

DATESYTD returns all dates from the start of the year up to the latest date in the current filter context. Therefore if used in a matrix with year on the columns then for previous years it returns the total.



Ben Dobbs

LinkedIn | Twitter | Blog

Did I answer your question? Mark my post as a solution! This will help others on the forum!
Appreciate your Kudos!!

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors