How to hide cumulative monthly values for future months?

[NoobBIuser]

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!

[amitchandak]

@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

[NoobBIuser]

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

[v-rzhou-msft · Accepted solution]

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.