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
TapZxK
Helper II
Helper II

Rolling Totals but hide Future Months

Hi All,

 

I have created a measure that allows me to compare Time Invest per month last FY & Current Fiscal. 

In the data set, I currently have all the data up until & including April 2023, when I put the measure on a clustered column chart visual or table visual the April numbers keep repeating until end of the Fiscal.

Is there a way to show future months as 0 until actual data comes in for those months?

My Fiscal Starts on 01-September

 

Rolling Total Hours =
VAR CurrentYearHours =
    CALCULATE ( [Total Time Invested], DATESYTD ( Calendar[Date], "08/31" ) )
RETURN
    TOTALYTD (
        CurrentYearHours,
        Calendar[Date],
        FILTER (
            ALL ( 'Calendar'[Date] ),
            'Calendar'[Date] >= DATE ( YEAR ( MAX ( 'Calendar'[Date] ) ), 9, 1 )
                && 'Calendar'[Date]
                    <= DATE ( YEAR ( MAX ( 'Calendar'[Date] ) ) + 1, 8, 31 )
        )
    )

 

TapZxK_1-1683750053990.png

 

TapZxK_2-1683750154035.png

 

Thank you for your help. 

1 ACCEPTED SOLUTION
Greg_Deckler
Super User
Super User

@TapZxK Try:

Rolling Total Hours =
VAR MaxDate = MAX( 'Calendar'[Date])
VAR CurrentYearHours =
    CALCULATE ( [Total Time Invested], DATESYTD ( Calendar[Date], "08/31" ) )
VAR TYTD = 
    TOTALYTD (
        CurrentYearHours,
        Calendar[Date],
        FILTER (
            ALL ( 'Calendar'[Date] ),
            'Calendar'[Date] >= DATE ( YEAR ( MAX ( 'Calendar'[Date] ) ), 9, 1 )
                && 'Calendar'[Date]
                    <= DATE ( YEAR ( MAX ( 'Calendar'[Date] ) ) + 1, 8, 31 )
        )
    )
VAR Result = IF( MaxDate > TODAY(), BLANK(), TYTD )
RETURN
Result

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

View solution in original post

1 REPLY 1
Greg_Deckler
Super User
Super User

@TapZxK Try:

Rolling Total Hours =
VAR MaxDate = MAX( 'Calendar'[Date])
VAR CurrentYearHours =
    CALCULATE ( [Total Time Invested], DATESYTD ( Calendar[Date], "08/31" ) )
VAR TYTD = 
    TOTALYTD (
        CurrentYearHours,
        Calendar[Date],
        FILTER (
            ALL ( 'Calendar'[Date] ),
            'Calendar'[Date] >= DATE ( YEAR ( MAX ( 'Calendar'[Date] ) ), 9, 1 )
                && 'Calendar'[Date]
                    <= DATE ( YEAR ( MAX ( 'Calendar'[Date] ) ) + 1, 8, 31 )
        )
    )
VAR Result = IF( MaxDate > TODAY(), BLANK(), TYTD )
RETURN
Result

@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Helpful resources

Announcements
LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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