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
kovacikm78
Frequent Visitor

Measure for max turnover for day or month and change it automatically based on date granularity

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?

DateYear/MonthTurnoverMax Daily TurnoverMax Monthly Turnover
01/01/20232023/011615
01/01/20232023/012615
01/01/20232023/015615
01/01/20232023/012615
01/02/20232023/015615
01/02/20232023/014615
01/02/20232023/016615
02/01/20232023/022615
02/01/20232023/022615
02/01/20232023/021615
02/02/20232023/021615
02/02/20232023/021615
02/02/20232023/021615
02/02/20232023/021615

 

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

2 ACCEPTED SOLUTIONS
v-rongtiep-msft
Community Support
Community Support

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

 

vpollymsft_0-1678944769566.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

kovacikm78
Frequent Visitor

thank you very much for your help

View solution in original post

2 REPLIES 2
kovacikm78
Frequent Visitor

thank you very much for your help

v-rongtiep-msft
Community Support
Community Support

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

 

vpollymsft_0-1678944769566.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.