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

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

Reply
NoobBIuser
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

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.

View solution in original post

3 REPLIES 3
amitchandak
Super User
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

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

 

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.

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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

MayPowerBICarousel

Power BI Monthly Update - May 2024

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