Forum Discussion

diogormatas's avatar
diogormatas
Frequent Visitor
10 years ago

DAX Applying Measure in Current Month

Hi guys, I have one Fact table with Assets, it's creation Date (connected to DIM_DATE) and asset location (DIM_LOCATION). What I am trying to achieve is something like this: MaxMon...
Need Help
MattAllington's avatar
MattAllington
Community Champion
10 years ago

As I understand, you only ever want to see 1 month of data. If the user doesn't select any period, you want the report to simulate as if the user had selected the latest month (as opposed to June hard coded). If a month is selected, then show at month. Regardless of anything else, only ever show one month. Is that correct?

diogormatas's avatar
diogormatas
Frequent Visitor
10 years ago

Exactly! 

This report is a stock analysis, so don't make sense in my report to allow more than one month selection. 

Thanks.

  • MattAllington's avatar
    MattAllington
    Community Champion
    10 years ago

    Ok. So technically we say "you want to select the last month in the current filter context". To do this, I would normally first ensure

    1. i had a calendar table
    2. the calendar table had a month ID column to uniquely identify each month in consecutive order
    3. Then write the following measure

     

    =calculate(COUNTROWS(V_F_ASSETS), max(calendar[monthID]))

     

    read about out calendar tables here http://exceleratorbi.com.au/power-pivot-calendar-tables/

  • diogormatas's avatar
    diogormatas
    Frequent Visitor
    10 years ago
    MattAllington wrote:

    Ok. So technically we say "you want to select the last month in the current filter context". To do this, I would normally first ensure

    1. i had a calendar table
    2. the calendar table had a month ID column to uniquely identify each month in consecutive order
    3. Then write the following measure

     

    =calculate(COUNTROWS(V_F_ASSETS), max(calendar[monthID]))

     

    read about out calendar tables here http://exceleratorbi.com.au/power-pivot-calendar-tables/

    MattAllington

     

    Hi, thanks for your help.

    Your sugestion return the error "A function 'MAX' has been used in a True/False expression" and after i tried this way:

     

    Current Month # Assets v4 = calculate(COUNTROWS(V_F_ASSETS), filter(V_DIM_TIME,max(V_DIM_TIME[COD_MONTH_ID])))

    Where COD_MONTH_ID is the concantenation between yyyymm, to have a unique MONTH ID. 

    But the output was strange and I could not interpret it.

    One last try, it matters the place/table that i added my meausure?  This measure relates data from FACT and TIME tables.

    Thanks all.

  • MattAllington's avatar
    MattAllington
    Community Champion
    10 years ago

    Wow, did I write that measure?!  Sorry about that.  

     

    Try this

     

    =
    CALCULATE (
        COUNTROWS ( V_F_ASSETS ),
        FILTER ( ALL ( calendar ), calendar[monthID] = MAX ( calendar[monthID] ) )
    )

  • diogormatas's avatar
    diogormatas
    Frequent Visitor
    10 years ago

     

    MattAllington wrote:

    Wow, did I write that measure?!  Sorry about that.  

     

    Try this

     

    =
    CALCULATE (
        COUNTROWS ( V_F_ASSETS ),
        FILTER ( ALL ( calendar ), calendar[monthID] = MAX ( calendar[monthID] ) )
    )

    MattAllington

    I fully agree with your approach! 

    But this one replicate my 1st measure behaviour when i don't select any month in the slicer i.e. it works on a card visualization (Aggregated data), but it doesn't work when I use it on a table with any other dimension.  

    The reason is the same, it doesn't return the data for MaxMonth, it returns the MaxMonth data for each specific dimension value. So in some cases instead of return 0 to June data, the measure is returning 2 assets in May.

    Thanks.