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
Eric_Zhang's avatar
Eric_Zhang
Microsoft Employee
10 years ago

diogormatas

 

Try

 

MaxMonthMonth = month(Calculate(MAX(V_F_ASSETS[asset_date]),All(V_F_ASSETS)))

Current Month # Assets = 
CALCULATE(
COUNTROWS(V_F_ASSETS),

filter(V_F_ASSETS,
MONTH(V_F_ASSETS[asset_date])= MONTH(MAX(V_F_ASSETS[asset_date]))
)
)
diogormatas's avatar
diogormatas
Frequent Visitor
10 years ago
Eric_Zhang wrote:

diogormatas

 

Try

 

MaxMonthMonth = month(Calculate(MAX(V_F_ASSETS[asset_date]),All(V_F_ASSETS)))

Current Month # Assets = 
CALCULATE(
COUNTROWS(V_F_ASSETS),

filter(V_F_ASSETS,
MONTH(V_F_ASSETS[asset_date])= MONTH(MAX(V_F_ASSETS[asset_date]))
)
)


Eric_Zhang I believe that was 1st try that MattAllington said. :)

This one always set the data to "always" June (my current max month) and if a user select May or other month at the slicer, the result will be empty.

This is why i was trying to solve it with "AllSelected". 

I really appreciate your help but maybe my request isn't supported by DAX now :/

  • MattAllington's avatar
    MattAllington
    Community Champion
    10 years ago

    I'm sure it is supported.  The issue I am having is that I am not clear what you want/need.  Can you please provide a number of scenarios with different slicer settings indicating what answer you expect.  I think if you can clarify this, it will be easier to give you an answer

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

    I'm sure it is supported.  The issue I am having is that I am not clear what you want/need.  Can you please provide a number of scenarios with different slicer settings indicating what answer you expect.  I think if you can clarify this, it will be easier to give you an answer


    Hi, let me try to clarify it.

     

    If the user don't select any month, i want to emulate the same behaviour like if he select June. 

    If the user select any month, then he should see this month.

    The two differents solutions at the moment i can:

    • Force the June data only and return zero if the user select any other month
    • Respect the month selected by the user, but if he don't select any month, the measure is retrieving the max month #Assets but by dimension value i.e. Logistics will return 1 Asset from May instead of 0 Assets from June.

     

    It was clear ? 

    Thanks for your help guys.

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