cancel
Showing results for
Did you mean:

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

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 )
)
)``````

1 ACCEPTED SOLUTION
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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
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``````

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors