Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Last month with data

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

Tips and Tricks
  • Reid_Havens's avatar
    Reid_Havens
    9 years ago

    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

5 Replies

  • Reid_Havens's avatar
    Reid_Havens
    Most Valuable Professional
    9 years ago

    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's avatar
      Anonymous
      Not applicable
      9 years ago

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

      • v-chuncz-msft's avatar
        v-chuncz-msft
        Community Support
        9 years ago

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