Max Vaue for each month and Sum up based on date slicer

[Varun511]

HI ,

Have below data  need to find out Max Actual Trans for each month and Sumup and i do have date slicer in the report

for suppose if select date slicer from March 1st till Apr 30th need to find max Actual trans for March and April and then sumup and show in report 

tried Maxx(table1,Actual trans) but not wokring can anyone help me this issue?

Any help would be highly appreciated 

[Anonymous · Accepted solution]

Hi @Varun511 ,

I found that you have multiple same dates with different Actual Trans, supposing that the max value you want to return is whether grouped by date returns the max value monthly.

Below is my solution for two situations.

Sample data

Create a calendar table.

Calendar =
ADDCOLUMNS (
    CALENDAR ( MIN ( 'Table'[Trans Date] ), MAX ( 'Table'[Trans Date] ) ),
    "Year", YEAR ( [Date] ),
    "Month", MONTH ( [Date] )
)

If you want to return the max value grouped by date for each month,

create a measure to get the sum grouped by date.

Sum per month =
CALCULATE (
    SUM ( 'Table'[Value] ),
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        [Month] = MAX ( 'Calendar'[Month] )
            && [Year] = MAX ( 'Calendar'[Year] )
    )
)

Then create another measure to get the max value for each month.

Max Actual Trans for each month1 =
MAXX (
    FILTER (
        ALLSELECTED ( 'Calendar' ),
        [Month] = MAX ( 'Calendar'[Month] )
            && [Year] = MAX ( 'Calendar'[Year] )
    ),
    [Sum grouped by date]
)

Sum up measure is as follows.

SumUp = CALCULATE(SUM('Table'[Value]),ALLSELECTED('Calendar'))

Best Regards,

Stephen Tao

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

[rohit_singh]

Hi @Varun511 ,

I have assumed the following to be my dataset

I have created a measure 

Max Trans = Maxx(ProcessTrans, ProcessTrans[Actual Trans])

which changes the max values correctly when using the slicer

What is the output that you're getting?

Kind regards

Rohit

[Varun511]

Rohit need to Sumup  both and show  in one line 650+630

[Varun511]

and also rohit i do have date table and am joining with main table

[rohit_singh]

Hi @Varun511 ,

Not sure if this is exactly as per your requirement but it does calculate the sum of max values per month

Sum Trans =

var _mindate = min(ProcessTrans[Trans Date])

var _maxdate = max(ProcessTrans[Trans Date])

var _minmonth = min(ProcessTrans[Start of Month])

var _maxmonth = max(ProcessTrans[Start of Month])

var _minsum = CALCULATE([Max Trans], FILTER(ALLSELECTED(ProcessTrans), ProcessTrans[Start of Month] = _minmonth))

var _maxsum = CALCULATE([Max Trans], FILTER(ALLSELECTED(ProcessTrans), ProcessTrans[Start of Month] = _maxmonth))

RETURN

_minsum + _maxsum

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[Varun511]

Rohit i have date table am using date from date slicer will this logic work? 

[rohit_singh]

Hi @Varun511 ,

Yes it does work for me when I filter using a date table instead of the trans date column.

Please ensure that you have joined trans date on the main table with the date field on the date table.

Kind regards,

Rohit

Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 😊

[Anonymous]

Hi Carun511

Do you have a Date table ?

[Varun511]

HI James,

Sorry forget to mention yes i do have data table and joining to main table