Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi guys,
please help me first to create a simple measures: max Turnover for day or month. This should be based on period of the date slicer.
I need this measure for calculating ratio.
I was then wondering, if it is possile to create a measure, that will change automtically to day or month based on selected granularity of x axis.
My sample data. New measures are Max Daily Turnover and Max Monthly Turnover that will return max sum of turnover for day or for month from date selected in the slicer.
Max daily turnover was in 01/02/2023: 6 and max monthly turnover was in 2023/02: 15
Can you help me to create this measures please?
Date | Year/Month | Turnover | Max Daily Turnover | Max Monthly Turnover |
01/01/2023 | 2023/01 | 1 | 6 | 15 |
01/01/2023 | 2023/01 | 2 | 6 | 15 |
01/01/2023 | 2023/01 | 5 | 6 | 15 |
01/01/2023 | 2023/01 | 2 | 6 | 15 |
01/02/2023 | 2023/01 | 5 | 6 | 15 |
01/02/2023 | 2023/01 | 4 | 6 | 15 |
01/02/2023 | 2023/01 | 6 | 6 | 15 |
02/01/2023 | 2023/02 | 2 | 6 | 15 |
02/01/2023 | 2023/02 | 2 | 6 | 15 |
02/01/2023 | 2023/02 | 1 | 6 | 15 |
02/02/2023 | 2023/02 | 1 | 6 | 15 |
02/02/2023 | 2023/02 | 1 | 6 | 15 |
02/02/2023 | 2023/02 | 1 | 6 | 15 |
02/02/2023 | 2023/02 | 1 | 6 | 15 |
And final question is, if it is possible to create a measure, that will return max daily turnover if Date is selected on x axis and Max monthly turnover if Year/Month is selected on x axis... It should be flexible according to granularity selected.
Thank you very much for your help
Solved! Go to Solution.
Hi @kovacikm78 ,
I have created a new sample, please refer to my pbix file to see if it helps you.
Create a new table with date.
Then create a measure.
Measure =
VAR _SELECTEDDATE =
SELECTEDVALUE ( 'date'[Date] )
VAR _MAXDATE =
CALCULATE (
MAX ( 'Table'[Turnover] ),
FILTER ( ALL ( 'Table' ), 'Table'[Date] = _SELECTEDDATE )
)
VAR _YEARMONTH =
SELECTEDVALUE ( 'date'[year/month] )
VAR _MAXYEARMONTH =
CALCULATE (
SUM ( 'Table'[Turnover] ),
FILTER ( ALL ( 'Table' ), 'Table'[Year/Month] = _YEARMONTH )
)
RETURN
IF (
_SELECTEDDATE <> BLANK ()
&& _YEARMONTH = BLANK (),
_MAXDATE,
IF (
_YEARMONTH <> BLANK ()
&& _SELECTEDDATE = BLANK (),
_MAXYEARMONTH,
MAX ( 'Table'[Turnover] )
)
)
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.
thank you very much for your help
Hi @kovacikm78 ,
I have created a new sample, please refer to my pbix file to see if it helps you.
Create a new table with date.
Then create a measure.
Measure =
VAR _SELECTEDDATE =
SELECTEDVALUE ( 'date'[Date] )
VAR _MAXDATE =
CALCULATE (
MAX ( 'Table'[Turnover] ),
FILTER ( ALL ( 'Table' ), 'Table'[Date] = _SELECTEDDATE )
)
VAR _YEARMONTH =
SELECTEDVALUE ( 'date'[year/month] )
VAR _MAXYEARMONTH =
CALCULATE (
SUM ( 'Table'[Turnover] ),
FILTER ( ALL ( 'Table' ), 'Table'[Year/Month] = _YEARMONTH )
)
RETURN
IF (
_SELECTEDDATE <> BLANK ()
&& _YEARMONTH = BLANK (),
_MAXDATE,
IF (
_YEARMONTH <> BLANK ()
&& _SELECTEDDATE = BLANK (),
_MAXYEARMONTH,
MAX ( 'Table'[Turnover] )
)
)
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.
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |