Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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:
Source | Month | MMM | Activity | What happens at moment: | |||
Plan | 1 | Apr | 2102 | Year To Date | |||
Plan | 2 | May | 2039 | Plan | 30,305 | ||
Plan | 3 | Jun | 2019 | Actual | 25,537 | ||
Plan | 4 | Jul | 2912 | ||||
Plan | 5 | Aug | 2900 | ||||
Plan | 6 | Sep | 2821 | ||||
Plan | 7 | Oct | 2839 | ||||
Plan | 8 | Nov | 2891 | What I need it to be: | |||
Plan | 9 | Dec | 2981 | Year To Date | |||
Plan | 10 | Jan | 2258 | Plan | 25,762 | ||
Plan | 11 | Feb | 2173 | Actual | 25,537 | ||
Plan | 12 | Mar | 2370 | ||||
Actual | 1 | Apr | 2077 | ||||
Actual | 2 | May | 2014 | ||||
Actual | 3 | Jun | 1994 | ||||
Actual | 4 | Jul | 2887 | ||||
Actual | 5 | Aug | 2875 | ||||
Actual | 6 | Sep | 2796 | ||||
Actual | 7 | Oct | 2814 | ||||
Actual | 8 | Nov | 2866 | ||||
Actual | 9 | Dec | 2956 | ||||
Actual | 10 | Jan | 2258 |
Many thanks for your help!
Kind regards
Solved! Go to Solution.
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
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.
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
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
81 | |
70 | |
62 |
User | Count |
---|---|
148 | |
116 | |
104 | |
90 | |
65 |