How to measure days between dates when beginning and ending are in different months?

[drivero]

Hi all!

I have a problem with my project. I am using Analysis Service and a model based with Tabular Editor, but I attached here a simplified project with the problem I want to solve.

https://we.tl/t-ixwq5zsVDn 

I want to create a measure which counts the days wasted for each register since beginning until end. This is easy, but the problem appears when the beggining is in one month and the end in the other.

What I would like my Table visual do is: when selecting March, shows the value of Empleado 1 and the measure should count 13 days (from 1 to 13/03) and when selecting February, the measure should count 2 (27 and 28th February). The problem is that is only one register, I do not have two registers and I cannot create it. I need to use only this data, but when I filter visual by Month, as i already have a relationship between DimDate and Fecha de inicio, in March, the register disapear.

thanks in advance for your help 🙂

[Ahmedx]

Based on your description, I created data to reproduce your scenario. The pbix file is attached in t...

CountDaysMonths.pbix

[drivero]

thanks @Ahmedx 

it works with the sample file.

But when I use this measure with Tabular Editor in Analysis Service, i do not know why is not working. I deleted the relation between table "DimDate" and table "Ausencias" but the result of the measure is like the total, does not separete for each month. Is there anything am I missing? You have created a table "Table" in your pbi file, do I need to create it as well?
thanks for you help.

[Ahmedx]

"...I need to create it as well?"
no i forgot to delete it

[drivero]

@Ahmedx any idea to use the same but in Analysis Service? because i cannot use parameters there

[Ahmedx]

Do you have a desk calendar? instead of DimDate you can use your calendar

[Ahmedx]

that's excluding weekends

[Ahmedx · Accepted solution]

Is this what you are looking for?

[drivero]

Yes, this is what I need. Both ways, including and excluding weekends. How did you do?

[drivero]

@Ahmedx is here, can you download it?
https://we.tl/t-ixwq5zsVDn 

[drivero]

Hello @Ahmedx 

I try it but it is still not working. Could you help me? Thanks.

[Ahmedx]

Share sample pbix file to help you.

[Ahmedx]

look at this, the principle of the solution is one
https://community.powerbi.com/t5/Desktop/Managing-absences-per-month/m-p/3165816#M1068662