Challenge in DAX... getting the numbers till selected date

[Anonymous]

Hi Experts,

So here is the challenege, and I believe is the most difficult one i faced till date.

So I have this table:

Org    |  Resource | Resource Count  | Start Date  | End Date

1              Ankur            1                   20-1-2015        20-1-2019

2             Aditya            1                   20-1-2017        13-3-2018

1             Sam               1                   11-4-2018         08-09-2018

1              Ram              1                   14-11-2016       09-10-2019

2            Kunal               1                  13-12-2016       11-11-2022   

1            Omni              1                   04-04-2018        11-10-2019

So as we see, we have resource count for different organization( like org 1 and 2 in example) . I want my dashboard to display the number of resources available in a organization on particular month and year.

For example if user select January 2018 he should get:

Org    |   Total Resource Count  

1                 2

2                 2

If user select April 2018 he should get:

Org    |   Total Resource Count  

1                 3

2                 1

As we see, I need the number of resources present in organization on that particular month and year. Please help me in acheiving the same. I have changed the format of date to Month and Year( like Jan-2015)

[v-qiuyu-msft]

Hi @Anonymous,

You can create a calendar table:

Calendar = CALENDAR("2015-1-1","2019-12-31")

Then create a measure below: 

Measure = CALCULATE(COUNT(Table1[Resource]),FILTER(ALLSELECTED(Table1),'Table1'[Start Date]<=MAX('Calendar'[Date]) && Table1[End Date]>=MAX('Calendar'[Date])&& Table1[Org]=MAX(Table1[Org])))

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi @v-qiuyu-msft

No comments on my query?

I tried to figure out but still not clear with below points:

1) What if My Org is a string instead of Number( MAX function wont work )
2) How you got Month Hireachy created in Slicer. I only see Year.
3) You have not selected Calendar date till 2022. is it by intentional or you have missed it. If intentional then how we plan to get data for 2021

Hope you respond soon.

Thanks

[v-qiuyu-msft · Accepted solution]

Hi @Anonymous,

1. The MAX() function works fine. See attached pbix file. 

2. I enabled preview feature "Show dates as a hierarchy (preview)", see: https://powerbi.microsoft.com/fr-fr/blog/power-bi-desktop-january-2018-feature-summary/#dateHierarchy

3. I just specify date range for calendar table based on the sample you describe in your original post. You can create a date range below, see attached pbix file. 

Calendar = CALENDAR(MIN('Table1'[Start Date]),MAX('Table1'[End Date]))

Best Regards,
Qiuyun Yu 

[Anonymous]

Hi @v-qiuyu-msft,

Also I see is that if I dont select any thing like my Year and month filter is empty, I get blank value in Measure for Org 1 and '2' as measure for Org 2.. HOW???

[Anonymous]

Hi @v-qiuyu-msft,

I dont believe we are getting the right result. Can you look at the query again and help me out. I am getting a count when I am not selecting anything and also in my tabular column i am getting a value as Total at the end and in most of cases it is taking it as the last row value. What is that we are missing?

Thanks

[Anonymous]

Hi @v-qiuyu-msft,

Also would like to understand how you have created date hirearchy for Month? I am unable to get it and when i am changing the date in calendar field, the Month hirearchy is lost. I can only see Year present in Date Hirerachy when selected. Please enlighten me!!

[Anonymous]

Hi @v-qiuyu-msft,

I believe if you have missed few points.

1. If you see in my input there is one row with resource name as Kunal whose end date is 2022. So if i select any month in 2021,  I should get the resource count. but the calendar we have created is till 2019 and we dont have option to select 2021 or 2022. Should i increase the Calendar year of the table.

2. If i select any month and year I get the resource count and Org but If i add the resource name in the visual, i get all the names even of those resource which are not counted. How to handle this?

[Anonymous]

Hi @v-qiuyu-msft,

I believe if you have missed few points.

1. If you see in my input there is one row with resource name as Kunal whose end date is 2022. So if i select any month in 2021,  I should get the resource count. but the calendar we have created is till 2019 and we dont have option to select 2021 or 2022. Should i increase the Calendar year of the table.

2. If i select any month and year I get the resource count and Org but If i add the resource name in the visual, i get all the names even of those resource which are not counted. How to handle this?