DAX Applying measure in current month

[newbie]

Hello Community,

I am new to Power BI. What I'm trying to achieve is the following:

I have an Active count measure that counts distinct active employee numbers in my master data. In this timeline bar chart below, if a particular month is selected, I wanted the other charts (eg. the breakdown chart below) to reflect data in the month selected. If there is no month selected, I wanted the other charts to show the latest (max) month data in the master data.

I'm currently using the MAX function to do this. However, the problem is that in the data breakdown it is not correlating with the timeline, i.e. some old data in historical months are showing up. For example, the 2 people in Business Transformation are not in the latest month dataset; they were in last month's dataset. If I click on the 2017/Feb bar, the 2 people would not show up (which is correct), however, it does not work properly if no month is selected.

Any help is appreciated!

[dkay84_PowerBI]

Well what is your DAX formula?  Are you using an IF or SWITCH statement logic to detect if no month is selected?

[newbie]

This is my DAX formula:

Actives = CALCULATE(DISTINCTCOUNT('HR MasterData'[Personnel Number]),FILTER('HR MasterData','HR MasterData'[In or Out]="1"),FILTER(ALL('HR MasterData'),'HR MasterData'[ReportPeriod]=MAX('HR MasterData'[ReportPeriod])))

I think the measure is calculating correctly (at the aggregate level). When there is no month on select, it is using the max Report Period. The problem is when I break down the details... As you can see in this chart below, the numbers don't add up to 713 (which is what Actives value was calculated as). The breakdown totals 715 - it is because those 2 people in Business Transformation, they were in this group in January, but not in February. I think the MAX is saying "within the given dataset, give me the max report period that each category corresponds to", so for those 2 folks, the max was 2017/Jan while for everybody else the max was 2017/Feb. So when I click on a particular month, let's say 2017/Feb, everything is working correctly...

I am sorry I can't explain my problem very well... I feel like the issue is the breakdown does not understand the timetable...

[newbie]

I also tried this:

- Created a measure:  OnSelect = HASONEVALUE('HR MasterData'[ReportPeriod])

this measure was able to tell me whether a particular month is selected, True or False

- Then I created another measure hoping to "store" the month that I need to report on:

Report Period OnSelect = if([OnSelect],VALUES('HR MasterData'[ReportPeriod]),CALCULATE(MAX('HR MasterData'[ReportPeriod]),ALL('HR MasterData')))

This field is also able to capture the correct report period that I should be reporting on.

But then I failed to pull the dataset where the line record's report period = [Report Period OnSelect]. I used a column to do the comparison, but it's giving me 1 for every line record...

[newbie]

My issue has been resolved. Thank you.

[v-huizhn-msft]

Hi @newbie,

Please mark the helpful reply as answer, or share your solution, so that more people can find workwroud form here. Thanks for understanding.

Best Regards,
Angelia