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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Creative_tree88
Helper III
Helper III

Actual YTD vs Planned Year Figures

Hi - I have the following which I'm trying to work out...I need to show a nice bar chart which shows where we are in terms of actual activity vs where we are currently against plan, for the same month in quesiton.  Obviously our plan goes up to month 12, but this cannot be included when comparing where we are cumulatively in the year against plan.

I've shown, below, the data I'm dealing with (sample) and also to the right what I currently get with basic count, vs what I need to show.  I'd like to do this in a calculated measure so that every time we enter a new month, it re-calculates and compares against the YTD plan too:

SourceMonthMMMActivity What happens at moment:
Plan1Apr2102 Year To Date 
Plan2May2039 Plan30,305 
Plan3Jun2019 Actual25,537 
Plan4Jul2912    
Plan5Aug2900    
Plan6Sep2821    
Plan7Oct2839    
Plan8Nov2891 What I need it to be:
Plan9Dec2981 Year To Date 
Plan10Jan2258 Plan25,762 
Plan11Feb2173 Actual25,537 
Plan12Mar2370    
Actual1Apr2077    
Actual2May2014    
Actual3Jun1994    
Actual4Jul2887    
Actual5Aug2875    
Actual6Sep2796    
Actual7Oct2814    
Actual8Nov2866    
Actual9Dec2956    
Actual10Jan2258    


Many thanks for your help!

Kind regards

1 ACCEPTED SOLUTION
v-rongtiep-msft
Community Support
Community Support

Hi @Creative_tree88 ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _month =
    DAY ( TODAY () )
VAR _endday =
    DAY ( EOMONTH ( TODAY (), 0 ) )
VAR _maxactual =
    MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Source] = "Actual" ), 'Table'[Month] )
VAR _re =
    IF (
        _month < _endday,
        CALCULATE (
            SUM ( 'Table'[Activity] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Source] = SELECTEDVALUE ( 'Table'[Source] )
                    && 'Table'[Month] <= _maxactual
            )
        ),
        CALCULATE (
            SUM ( 'Table'[Activity] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Source] = SELECTEDVALUE ( 'Table'[Source] )
                    && 'Table'[Month] < _maxactual
            )
        )
    )
RETURN
    _re

vpollymsft_0-1677476180892.png

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Polly

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

1 REPLY 1
v-rongtiep-msft
Community Support
Community Support

Hi @Creative_tree88 ,

I have created a simple sample, please refer to my pbix file to see if it helps you.

Create a measure.

Measure =
VAR _month =
    DAY ( TODAY () )
VAR _endday =
    DAY ( EOMONTH ( TODAY (), 0 ) )
VAR _maxactual =
    MAXX ( FILTER ( ALL ( 'Table' ), 'Table'[Source] = "Actual" ), 'Table'[Month] )
VAR _re =
    IF (
        _month < _endday,
        CALCULATE (
            SUM ( 'Table'[Activity] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Source] = SELECTEDVALUE ( 'Table'[Source] )
                    && 'Table'[Month] <= _maxactual
            )
        ),
        CALCULATE (
            SUM ( 'Table'[Activity] ),
            FILTER (
                ALL ( 'Table' ),
                'Table'[Source] = SELECTEDVALUE ( 'Table'[Source] )
                    && 'Table'[Month] < _maxactual
            )
        )
    )
RETURN
    _re

vpollymsft_0-1677476180892.png

How to Get Your Question Answered Quickly 

 

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

 

Best Regards
Community Support Team _ Polly

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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