Forum Discussion
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:
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.
22 Replies
Hi diogormatas
try with this little change:
Current Month # Assets =
CALCULATE(
COUNTROWS(V_F_ASSETS);
filter(ALL(V_F_ASSETS);
MONTH(V_F_ASSETS[asset_date])=[MaxMonthMonth]
)
)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.
Hi Matt,
I used the ALLSELECTED to get the max Month selected by the user or max month in Fact data if they don't select any month in the slicer.
I don't have sure if this behaviour is possible. It is? :/
Your solution always give the June data as output. But if a user select May, it will give me 0 for all Dimension values because the intersection is done between May and June will be empty i guess.
Thanks.
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.
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.
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?
