DAX Applying Measure in Current Month

[diogormatas]

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:

MaxMonth: represents the max date at Fact table;
Current Moth # Assets: Should represent the number of assets on MaxMonth (6).

My problem is that my measure actually is counting the # of Assets on MaxMonth but for each Location, i.e. At logistics it counts the May Assets (because MaxMonth for Logistics at Fact table = 5) .

Expressions:
MaxMonthMonth = month(Calculate(MAX(V_F_ASSETS[asset_date]);Allselected(V_F_ASSETS)))

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

filter(V_F_ASSETS;
MONTH(V_F_ASSETS[asset_date])=[MaxMonthMonth]
)
)

 Note: 
I have one Month Slicer and this measure is to prevent the calculation works well even if the user don't select any month.

Thanks all.

[Eric_Zhang]

@diogormatas

Try a measure as below, it would not be affected by Slicer.

Current Month # Assets =
CALCULATE (
    COUNTROWS ( V_F_ASSETS ),
    LASTDATE ( ALL ( V_F_ASSETS[asset_date] ) )
)

If you have any question, feel free to let me know.

[diogormatas]

Hi Eric,

Your solution give me the same behaviour as Matt's.

Maybe because the relationship I've defined in the Model between FACT table and TIME dimension is with "both" cross filter direction.

I came from QlikView apps, maybe I am trying to replicate something that is not usual in Power BI Apps.

In the bottom line, I just want to forearm the user behaviour. If they don't select any month, I automatically calculate "Current Month" data, but if the user select April for example, I just show April data.

Thanks all guys.

[Eric_Zhang]

---

In the bottom line, I just want to forearm the user behaviour. If they don't select any month, I automatically calculate "Current Month" data, but if the user select April for example, I just show April data.

---

Then your measures seem working this way, what is wrong with them?

[diogormatas]

---

@Eric_Zhang wrote:

---

In the bottom line, I just want to forearm the user behaviour. If they don't select any month, I automatically calculate "Current Month" data, but if the user select April for example, I just show April data.

---

Then your measures seem working this way, what is wrong with them?

---

Basically my version works in this cases:
- When the user select any specific Month.
- When the user don't select any Month but all dimension values have values to MaxMonth (June in this case)

• But it doesn't work now because Logistics and Salesman only have data for May

Sugested solutions just works if the user don't select any month or select current max Month (June in this case).

• If i select any other month, it will show zero for all values, because the intersection between other months and June will be empty,
  
  Thanks guys.

[Eric_Zhang]

@diogormatas

So what would you like to show for Logistics and Salesman only having data for May when Jun is selected?

[diogormatas]

@Eric_Zhang

I thought that i am trying to replicate a natural behaviour:

If the user select any month, i want to show the #Assets for that month, regardless the dimension that the table has.

So, In that case i want to show zero, because Logistics and Salesman doesn't have Assets in that month. But the main problem is when the user don't select any month and i want to "emulate" a selection to MaxMonth (June in present data). And here, my measure is retrieving May data for the cases in which doesn't exist in June. 

In your opinion, this is not a natural behaviour for the user?

Thanks.

[Mass]

I want to display QTD as of previous month means In june, the value should be only for Apr and May. Can anyone give me the formula. The below one is giving QTD not as of previous month

QTD PrevMonth =
CALCULATE (
    [Total Production],
    FILTER (
        ALL ( 'Months'[Month] ),
        'Months'[Month] <= MAX(Months[Month]) )
    )

[Eric_Zhang]

@diogormatas

Change the MaxMonthMonth as

MaxMonthMonth = MONTH(MAX(V_F_ASSETS[asset_date]))

When user don't select any month, it shows latest month for each location. When user select a specific month, the visual shows only the locations which have data for the month(the locations having no data in the month don't show up, instead of show count 0), is it acceptable?

[diogormatas]

---

@Eric_Zhang wrote:

@diogormatas

Change the MaxMonthMonth as

MaxMonthMonth = MONTH(MAX(V_F_ASSETS[asset_date]))

When user don't select any month, it shows latest month for each location. When user select a specific month, the visual shows only the locations which have data for the month(the locations having no data in the month don't show up, instead of show count 0), is it acceptable?

---

Hi Eric_Zhang,

Thanks for your help.

This is the behaviour i've got at first time.  Now here is my "million dollar question"....  

Is it possible in DAX change this: "When user don't select any month, it shows latest month for each location." to this "When user don't select any month, it shows latest GLOBAL month independently the location. "?

I am new in DAX and i am trying to know what is achievable or not.

Thanks guys.

[Eric_Zhang]

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

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

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]

---

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

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]

Exactly! 

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

Thanks.

[MattAllington]

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]

---

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

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]

---

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

[MattAllington]

What happens if you swap ALLSELECTED in your first measure to ALL?  ALLSELECTED Is a tricky little sucker, and I suspect that may be the issue.