Last month with data

[Anonymous]

Hi,

I created this simple measure which works as intended:

FORMAT (
                LASTNONBLANK ( 'Calendar'[Date]; COUNTROWS ( RELATEDTABLE ( 'Facttable' ) ) );
                "MMM YYYY"
            )

The last month that holds transactions in  Facttable is May 2017, so "May 2017" is returned by the formula.

Now I want to translate the same logic to a calculated collumn in my Calendar/Date table:

IF (
    FORMAT (
        LASTNONBLANK ( 'Calendar'[Date]; COUNTROWS ( RELATEDTABLE ( 'Facttable' ) ) );
        "YYYYMM"
    )
        = FORMAT ( 'Calendar'[Date]; "YYYYMM" );
    TRUE;
    FALSE
)

I would expect it to return "TRUE" only for May 2017, instead it returns "TRUE" for all months/dates that holds transations in the Facttable, so obviously the validation part is going wrong - I have played around with MAX, CALCULATE etc, but I can't seem to get it quite right.

Any help is much appreciated.

/RSK

[Reid_Havens · Accepted solution]

Hi there,

Happy to help. So the way around this is using the ALL() function inside of the filter context so it forces the calculation to look at the table rather than row by row. If I was writing this it would look like something below, I also used MAX instead of LASTNONBLANK (personal preference). Let me know if this works for you.

=
IF (
    FORMAT (
        CALCULATE (
            MAX ( 'Date Table'[Date] ),
            FILTER (
                ALL ( 'Date Table' ),
                COUNTROWS ( RELATEDTABLE ( FactOrderProduct ) ) <> BLANK ()
            )
        ),
        "YYYYMM"
    )
        = FORMAT ( 'Date Table'[Date], "YYYYMM" ),
    TRUE (),
    FALSE ()
)

Reid Havens - Principal Consultant

PowerPivotPro

[Anonymous]

Good one.. Will test it as soon as I get the chance. hopefully beginning of next week. Thanks!

[v-chuncz-msft]

@Anonymous,

You may refer to the DAX below.

Flag =
IF (
    FORMAT (
        MAXX (
            FILTER (
                VALUES ( 'Calendar'[Date] ),
                COUNTROWS ( RELATEDTABLE ( Facttable ) ) <> BLANK ()
            ),
            'Calendar'[Date]
        ),
        "YYYYMM"
    )
        = FORMAT ( 'Calendar'[Date], "YYYYMM" ),
    TRUE (),
    FALSE ()
)

[Anonymous]

Okay - I had a little time to test these suggestions. Both returns the last date in the Fact, but ideally they should return the last date where column xx (column containing numerics) is not blank. I will try to modify myself a little later today and post the final code, unless someone here is faster 🙂 

[Anonymous]

Ok, a small adjustment, and this one takes into account a specific column:

= 
IF (
    FORMAT (
        CALCULATE (
            MAX ( 'Date'[Date] ),
            FILTER (
                ALL ( 'Fact' ),
                Fact[SalesQuantity] <> BLANK ()
            )
        ),
        "YYYYMM"
    )
        = FORMAT ( 'Date'[Date], "YYYYMM" ),
    TRUE (),
    FALSE ()
)

Thanks for the answers, creds go to the first answer above 🙂