cancel
Showing results for
Did you mean:

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

New Member

## How to hide cumulative monthly values for future months?

Hi,

I have a graph which shows a projected monthly value (i.e., Jan to June) and the actual monthly running value. As we're in March, the cumulative is the same from March onwards, which I want to hide.

There's two date columns (planned/actual date), which I've linked to a general date table and I've put USERELATIONSHIP in my CALCULATE formulas.

For example, it would be something like this:

Table1

Planned Date | Actual Date | Total items

 01/01/2022| 05/01/2022| 20

Date Table labelled as "Calendar"

and I would have: x = CALCULATE(SUM(Table1[Total items]),USERELATIONSHIP('Calendar'[Date],Table1[Actual Date]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date])))

• I don't think I even need the userelationship here

I have another measure like 'x' above but it's using planned date and that extends with values for all the future months, so I can't hide the other months or filter them off.

Example drawing of scenario

Any help is appreciated!

1 ACCEPTED SOLUTION
Community Support

Hi @NoobBIuser ,

If there is an active relationship between 'Calendar'[Date] and 'Table1'[Actual Date], you don't need to use USERLATIONSHIP function in your code. I have checked your screenshot and I know that you don't want your measure show results in future months (Apr, May...). You can try IF function to achieve your goal.

``````x =
VAR _CURRENTMONTH =
EOMONTH ( TODAY (), -1 ) + 1
RETURN
IF (
MIN ( 'Calendar'[Date] ) <= _CURRENTMONTH,
CALCULATE (
SUM ( Table1[Total items] ),
USERELATIONSHIP ( 'Calendar'[Date], Table1[Actual Date] ),
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
)``````

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Super User

@NoobBIuser , Try like

CALCULATE(SUM(Table1[Total items]),FILTER(ALL('Calendar'[Date]),'Calendar'[Date]<=MAX('Calendar'[Date]) && 'Calendar'[Date] <= eomonth(today(),0)))

USERELATIONSHIP is only needed if join in inactive

New Member

Hi Amit,

Thank you for the quick response. I tried the formula but it is returning the same outcome as when I omit the component with && onwards (in that it shows the cumulative values for April/May etc.).

I'm unfamiliar with all the functions and typed in a random set of code from a different site which appears to work, but I'm worried it's a one off working scenario.

Would you happen to know why this may be working? Code below:

x = IF(MIN('Calendar'[Date]) <= CALCULATE(MAX('Calendar'[Date]),Table1[Total items]>0),

CALCULATE(SUM(Table1[Total items],DATESYTD('Calendar'[Date])))

Community Support

Hi @NoobBIuser ,

If there is an active relationship between 'Calendar'[Date] and 'Table1'[Actual Date], you don't need to use USERLATIONSHIP function in your code. I have checked your screenshot and I know that you don't want your measure show results in future months (Apr, May...). You can try IF function to achieve your goal.

``````x =
VAR _CURRENTMONTH =
EOMONTH ( TODAY (), -1 ) + 1
RETURN
IF (
MIN ( 'Calendar'[Date] ) <= _CURRENTMONTH,
CALCULATE (
SUM ( Table1[Total items] ),
USERELATIONSHIP ( 'Calendar'[Date], Table1[Actual Date] ),
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
)``````

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.