Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

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.

Reply
Namoh
Post Partisan
Post Partisan

Adding WHERE MAX statement into DAX measure TOTALYTD

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.

TOTALYTD.png

 

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

Latest_Month.png 

 

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.

 

1 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.

 

It_works.png

 

 

 

View solution in original post

26 REPLIES 26
v-zhenbw-msft
Community Support
Community Support

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.

 

Add1.jpg

 

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.

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. 

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

 

Current_month_ok_summarise_nok.png

 _1_Total_Downtime_M_2_YTD_Total_Downtime_Target_M
woensdag 1 april 20209595
vrijdag 1 mei 2020104199
maandag 1 juni 2020101300
woensdag 1 juli 202089389
zaterdag 1 augustus 202020 
dinsdag 1 september 202082 
donderdag 1 oktober 2020105 
zondag 1 november 202096 

 

 

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
    )

 

add1.jpg

 

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.

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

 

Any clue where my mistake is?

 

 

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.

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
)

 

2_formulas_both_SUM_not_working.png

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.

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.

Hi @Namoh ,

 

We create a sample like you said.

 

add1.jpg

 

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)

 

add2.jpg

 

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.

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?

 

 

_4_The first measure.png

 

_5_YTD_Unplanned_Downtime_Target M.png

 

Using_Targets-Begin van de maand.png

 

Using_Calendar-Date.png

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)

 

adding1.jpg

 

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.

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 _1_.png

 

Measure _2_:

Measure _2_.png

 

 

 

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

 

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.

 

It_works.png

 

 

 

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
Community Champion
Community Champion

@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

 

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.

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

 

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

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

March 2024 PBI Gallery Image

Power BI Monthly Update - March 2024

Check out the March 2024 Power BI update to learn about new features.