Adding WHERE MAX statement into DAX measure TOTALYTD

[Namoh]

Hi.

I’ve got a DAX measure which calculates the TOTALYTD: YTD_Unplanned_Downtine_Target = IF( SUM(Targets[Target Unplanned downtime (hrs)]) = BLANK(); BLANK ();TOTALYTD(SUM(Targets[Target Unplanned downtime (hrs)]);'CALENDAR'[Date];ALL('CALENDAR');"3/31"))

This works fine. But when I put this in a table it will show all figures for the whole year, see picture 1.

I would like to only show the results uptill the current month, so I can compare the targets with the actuals, without having to use/change the filter each month.

I’ve made a measure which looks up the latest month, see picture 2: Latest Month = MAX(kpiexport[Month])

 

How to merge both measures into one measure so that the TOTALYTD, for the future months, wont show in my table?

I assume I have to combine the MAX with somekind of WHERE statement?

If another method is possible, I’m also interested in hearing this.

[v-zhenbw-msft]

Hi @Namoh ,

Do you want to calculate the total from the beginning of the year to the maximum date?

If yes, maybe you can try this measure.

Measure = 
var _lastest_date = MAX('Table (2)'[Date])
return
CALCULATE(SUM('Table'[value]),FILTER(ALLSELECTED('Table'),'Table'[Date]<=_lastest_date))

In the sample, the latest date is 2020/7/1, so the total calculates the value of 2020/1/1 – 2020/7/1.

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[Namoh]

Hi, this is almost what I'm looking for.

But instead of calculating from the beginning of the year, I need to calculated from the beginning of our fiscale year, which starts at 01-apr. 

[Namoh]

I noticed that my table showed incorrect dates (it showed jan-2020, while this should have been jan-2021).

I corrected my data, used the above formula and now it cuts of nicely at the current month.

But now it isn't summarizing anymore, as you can see in the picture below.

Both columns show exactly the same numbers, while the 2nd column should be a summarisation. like the table.

Where in the formula am I missing something?

Anyone who can help with the last missing part?

Formula/measure column 1: 

_1_Total Downtime M = SUM ( Targets[Target Unplanned downtime (hrs)] )

Formula/measure column 2:  

_2_YTD_Unplanned_Downtine_Target M =

VAR MaxVisibleDate =

MAX ( kpiexport[Month] )

VAR MaxYear =

YEAR ( MaxVisibleDate )

VAR DatesLessThanMaxDate =

FILTER (

ALL ( Calendar );

Calendar[Date] <= MaxVisibleDate

&& Calendar[Year] = MaxYear

)

VAR Downtime =

CALCULATE ( [_1_Total Downtime M]; DatesLessThanMaxDate )

VAR Result =

IF ( [_1_Total Downtime M] <> BLANK (); Downtime )

RETURN

Result

	_1_Total_Downtime_M	_2_YTD_Total_Downtime_Target_M
woensdag 1 april 2020	95	95
vrijdag 1 mei 2020	104	199
maandag 1 juni 2020	101	300
woensdag 1 juli 2020	89	389
zaterdag 1 augustus 2020	20
dinsdag 1 september 2020	82
donderdag 1 oktober 2020	105
zondag 1 november 2020	96

[v-zhenbw-msft]

Hi @Namoh ,

You need to calculate the data between 2020/1/1 and 2020/7/1, then accumulate it.

Please refer the following measure.

_2_YTD_Unplanned_Downtine_Target M = 
VAR _max_date =
    MAX ( kpiexport[Date] )
VAR _Total_downtime =
    CALCULATE (
        [_1_Total Downtime M],
        FILTER (
            'Table',
            'Table'[Date] <= _max_date
                && 'Table'[Date] >= DATE ( 2020, 4, 1 )
        )
    )
RETURN
    SUMX (
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Date] <= MAX ( 'Table'[Date] ) ),
        _Total_downtime
    )

If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[Namoh]

When I try your solution, I'm getting an error but can't figure out why.

Any clue where my mistake is?

[v-zhenbw-msft]

Hi @Namoh ,

May be the cause of the comma, you need to re-enter the comma in the red position to convert it to an English comma.

And you need to check the CALENDAR[Date] is Date type.

Or you can copy the formula to this website first, format it, and then copy it to your file.

Best regards,

Community Support Team _ zhenbw

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

[Namoh]

Yes, I forgot that I'm in a Dutch version which requires ; instead of ,

So I tried your formula (named: _3_) and the one that I already had (named: _2_), and they both stop nicely at the current date from the actuals.

But neither does the SUM, and your formula also returns different values...?

If I could make the SUM work in the _2_ formula I'm done.

Any idea's for that formula why the SUM isn't working?

Formula/measure _2_: 

_2_YTD_Unplanned_Downtine_Target M =

VAR MaxVisibleDate =

MAX ( 'kpiexport'[Month] )

VAR MaxYear =

YEAR ( MaxVisibleDate )

VAR DatesLessThanMaxDate =

FILTER (

ALL ( 'CALENDAR' );

'CALENDAR'[Date] <= MaxVisibleDate

&& 'CALENDAR'[Year] = MaxYear

)

RETURN

SUMX (

FILTER ( ALLSELECTED ( 'CALENDAR' ); 'Calendar'[Date] <= MAX ( 'kpiexport'[Month] ) );

[_1_Total Downtime M]

)

Formula/measure _3_:

_3_YTD_Unplanned_Downtine_Target M =

VAR _max_date =

MAX ( 'kpiexport'[Month] )

VAR _Total_downtime =

CALCULATE (

[_1_Total Downtime M];

FILTER (

'CALENDAR';

'CALENDAR'[Date] <= _max_date

&& 'CALENDAR'[Date] >= DATE ( 2020; 4; 1 )

)

)

RETURN

SUMX (

FILTER ( ALLSELECTED ( 'CALENDAR' ); 'CALENDAR'[Date] <= MAX ( 'kpiexport'[Month] ) );

_Total_downtime

)

[v-zhenbw-msft]

Hi @Namoh ,

If your Axis puts Calendar Date, you need to change the last MAX ('kpiexport'[Month])) to MAX('Calendar'[Date]).

_2_YTD_Unplanned_Downtine_Target M =
VAR MaxVisibleDate =
MAX ( 'kpiexport'[Month] )
VAR MaxYear =
YEAR ( MaxVisibleDate )
VAR DatesLessThanMaxDate =
FILTER (
ALL ( 'CALENDAR' );
'CALENDAR'[Date] <= MaxVisibleDate
&& 'CALENDAR'[Year] = MaxYear
)
RETURN
SUMX (
FILTER ( ALLSELECTED ( 'CALENDAR' ); 'Calendar'[Date] <= MAX ( 'Calendar'[Date] ) );
[_1_Total Downtime M]
)

Since the fields you used to create this Matrix table are not clear to us, could you please provide a mockup sample based on fake data or describe the fields of each tables and the relations between tables simply?

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

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

[Namoh]

I've made the suggested change but no change in the Matrix, still shows the same numbers.

I’ll try to explain the fields and the relations between the tables.

I’ve got 3 tables related to this Matrix table:

• Kpiexport
• Calendar
• Targets

They’ve the following relations:

• Kpiexport (month) -> Calendar (Date)  (many to one)
• Targets (Begin van de maand) -> Calendar (Date) (many to one)

Table kpiexport contains the actual data that’s being refreshed monthly.

Field kpiexport-Month in this table is the field uptill which I want to see the data in the Matrix table. This field is of type Date.

Table calendar is the calendar table which links the other two tables.

Field calendar-Date is of type Date.

Table targets contains the targets for the coming fiscal year per month.

Field targets-Begin van de maand identifies the months for which the targets apply, and is of type Date.

Is this sufficient info for now or is more needed?

Thanks in advance.

[v-zhenbw-msft]

Hi @Namoh ,

We create a sample like you said.

We can create two measures to meet your requirement.

The first measure = 
VAR _max_date =
    MAX ( kpiexport[Date] )
VAR _Total_downtime =
    CALCULATE (
        SUM(Targets[DownTime]),
        FILTER (
            'Targets',
            'Targets'[Date] <= _max_date
                && 'Targets'[Date] >= DATE ( 2020, 4, 1 )
        )
    )
RETURN
_Total_downtime

_3_YTD_Unplanned_Downtine_Target M = 
var _result = 
CALCULATE([The first measure],FILTER(ALLSELECTED('Calendar'),'Calendar'[Date]<=MAX(Targets[Date])))
return
IF(
    ISBLANK([The first measure]),BLANK(),_result)

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[Namoh]

Thanks for your help, but it still didn't work (at first).

I was really getting frustrated.

Then I noticed that I was using the "Target-Begin van de maand" field voor the Date and not the "Calendar-Date" field like you.

When using the "Calendar-Date" field it does summarize!  But this field has every day in the month in it, while field "Target-Begin van de maand" only has the 1st day of the month. 

And when using "Calendar-Date" it also shows 2018, 2019 etc, so not just this Fiscal Year.

So how to show only the Dates which has values in it?

[v-zhenbw-msft]

Hi @Namoh ,

Please try this measure to replace _5_YTD_Unplanned_Downtine_Target M.

_5_YTD_Unplanned_Downtine_Target M = 
var _result = CALCULATE([The first measure],FILTER(ALLSELECTED('Targets'),'Targets'[Date]<=MAX(Targets[Date])))
return
IF(
    ISBLANK([The first measure]),BLANK(),_result)

If it doesn't meet your requirement, could you simulate some data to create this Matrix table?

Or you can create some sample data based on our pbix file.

Best regards,

Community Support Team _ zhenbw

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

BTW, pbix as attached.

[Namoh]

I tried your change but result was the same.

I don't think the measure _5_ is the issue.

I think the issue lies in measure _2_ because this has a value for each day and therefore the table matrix shows each day instead of only the 1st of each month (or is my logic wrong).

Measure _1_ (used for measure _2_):

Measure _2_:

And I can't find a filter option to only show the 1st of each month.

[Namoh · Accepted solution]

Ok, so to be honest, I don't know what I did to make it work, but it works!

I was fooling around with some fields (Calendar-Date vs Targets-Begin van de maand), I was adding a Filter onto measure _5_ stating show all that's Not Empty, I clicked in the table in the right top corner on Zoom Out and Expand 1 level in Hierarchie, etc and now it shows:

1. just the values of the beginning of the months

2. it summarizes after each month

3. it stops after the current month

Thanks for your help.

[v-zhenbw-msft]

Hi @Namoh ,

Glad it is worked.

You can publish your formula and Mark the answer to let more people see and end this thread.

Best regards,

Community Support Team _ zhenbw

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

[AntrikshSharma]

@Namoh  If the goal is to create a running total then you can use this:

Create a separate measure for SUM ( Targets[Target Unplanned downtime (hrs)] )

Total Downtime = SUM ( Targets[Target Unplanned downtime (hrs)] )

YTD_Unplanned_Downtine_Target =
VAR MaxVisibleDate =
    MAX ( Dates[Date] )
VAR MaxYear =
    YEAR ( MaxVisibleDate )
VAR DatesLessThanMaxDate =
    FILTER (
        ALL ( Dates ),
        Dates[Date] <= MaxVisibleDate
            && Dates[Calendar Year Number] = MaxYear
    )
VAR Downtime =
    CALCULATE ( [Total Downtime], DatesLessThanMaxDate )
VAR Result =
    IF ( [Total Downtime] <> BLANK (), Downtime )
RETURN
    Result

[Namoh]

I've added 2 columns.

One with: _1_Total Downtime = SUM ( Targets[Target Unplanned downtime (hrs)] )

The other one with:  _2_YTD_Unplanned_Downtine_Target =
VAR MaxVisibleDate =
MAX ( Calendar[Date] )
VAR MaxYear =
YEAR ( MaxVisibleDate )
VAR DatesLessThanMaxDate =
FILTER (
ALL ( Calendar );
Calendar[Date] <= MaxVisibleDate
&& Calendar[Year] = MaxYear
)
VAR Downtime =
CALCULATE ( [_1_Total Downtime]; DatesLessThanMaxDate )
VAR Result =
IF ( [_1_Total Downtime] <> BLANK (); Downtime )
RETURN
Result

but I get an error message. Column _1_Total_Downtime not found or can't be used in this expression.

[AntrikshSharma]

@Namoh  Nope, you have to create measures, you are doing running total in a visual right? 🙂

[Namoh]

I've added two DAX columns (measures), nothing in a visual yet

[AntrikshSharma]

I your previous reply you have mentioned "I've added 2 columns."