Hi all,
I really need your help.
This is what I need after selecting a year in the Slicer, for example 2018.
I need to calculate the days on each line between StartOfPeriod and EndOfPeriod, but with the following conditions:
If StartOfPeriod <1/1/2018 then it starts counting only on 1/1/2018;
If EndOfPeriod> 12/31/2018 then the count ends on 12/31/2018
Thanks
Hi @JoaoMonteiro. Sounds like DAX can do this easily … can you attach a dataset and your work-in-progress PBIX file? Also, I’m guessing you want the period restricted to whatever year you have selected in your slicer (your example is for 2018 … should all periods use the same filters for 2018?).
Greg
Hi @Greg
What I Sample.pbix (440.4 KB) which is the following:
When I select the year 2018 I intend to perform the calculation on all rows of all elements whose EntryDate is less than 01/01/2018 and ExitDate is greater than 01/01/2018 or whose EntryDate is greater than 01/01/2018 but the EntryDate is less than 12/31/2018.
Thanks
OK … understood … what about when 2019 is selected?
Sorry @JoaoMonteiro. I don’t see the table in question nor the data in your sample … can you check?
Greg
Only can be selected one year at a time.
OK … still don’t see the data … can you please ensure that the correct sample was attached?
Greg
The sample is very small and simple, like
Sample.pbix (440.5 KB)
You can only select one year at a time
Sorry … I’m missing something … your original question asked about StartOfPeriod and EndOfPeriod and had a table with Id and Iup … is this a different dataset/PBIX?
Greg
The sample I´ve send to you only have a table with this content.
I downloaded and I only see these columns
Hi @JoaoMonteiro ,
Please note that this is not an on-demand forum, so it might take some time for members who give up their free time to provide support, to have a chance to review and respond to your question.
I’ve just taken a look and give this a go.
Count Days =
VAR YearSelected = VALUES( DiscDate[Year] )
VAR DatesInYear = CALCULATETABLE( VALUES('Date'[Date]), 'Date'[Year] IN YearSelected )
VAR StartDate = SELECTEDVALUE( RpgResources[EntryDate] )
VAR EndDate = SELECTEDVALUE( RpgResources[ExitDate] )
VAR ListDates = CALCULATETABLE( VALUES('Date'[Date]), 'Date'[Date] >= StartDate && 'Date'[Date] <= EndDate )
RETURN
COUNTROWS(
INTERSECT( DatesInYear, ListDates )
)
.
Here’s your sample file. Sample (5).pbix (417.7 KB)
I hope this is helpful
I’m really sorry and I ask you to accept my apologies if I was too insistent.
I really appreciate your work and your support.
Believe me that one day I would like to be in your role to be able to collaborate and help this whole community.
My sincere thanks.
Hi all,
I understood the idea Melissa shared but I couldn’t apply it correctly.
Would it be possible to help me?
I have been stuck on this problem for more than a week.
Thank you very much for your understandingSample (5).pbix (471.3 KB)
Regards
JoaoM
Hi @JoaoMonteiro,
Based on your logic: For example, for the month of Jun 2017, the headcount is all rows with EntryDate less than 1 Jun and ExitDate greater than 30 Jun
Head Count =
VAR MOY = MAX( 'Date'[MonthInCalendar] )
VAR PeriodStart = DATE( YEAR( MOY ), MONTH( MOY ), 1)
VAR PeriodEnd = EOMONTH( MOY, 0)
VAR Employees = CALCULATETABLE( VALUES( RpgResources[LastName] ), RpgResources[EntryDate] < PeriodStart, RpgResources[ExitDate] > PeriodEnd, REMOVEFILTERS('Date') )
RETURN
IF( VALUES( 'Date'[MonthInCalendar] ) IN VALUES( DiscDate[Month in Year] ),
COUNTROWS(
Employees
)
)
.
With this result
Here’s your sample file. Sample (5) (1).pbix (472.6 KB)
I hope this is helpful.
Hi @Melissa,
Your solution was not exactly what I needed but it was the push to get there.
You can’t imagine how grateful I am.
Now a piece of advice, please.
I’ve been working on Power BI and DAX for just over 6 months.
Despite many hours of study, I am still at a very early stage.
I have taken many of the DNA Enterprise courses.
How can I, in your opinion, evolve more quickly?
I would like to be really good, almost like you.
Regards
JoaoM
Hi @JoaoMonteiro,
Glad to hear you’ve resolved it! 
.
For eDNA courses the learning map gives a good idea on how to work through the vast content.
https://enterprisedna.co/learning-map/
And @BrianJ did a great video on the subject, you can find that here.
All the best.
To add to Melissa’s response, I would say the following:
Watching the Enterprise DNA videos will provide you a great foundation but alone will only take you so far. Watching videos is an inherently passive experience, and needs to be reinforced by lots and lots of repetition.
Your Enterprise DNA membership provides you three awesome ways to get those reps:
Be sure to stretch yourself into new areas, even at the risk of failing on some of your stretch goals. An amazing example of this was @Alex7891’s entry in Data Challenge #11
Participate in the Problem of the Week – and not only work to provide your solution on the problem, but also review the other solutions that come in and learn from their approaches, since often there are many different ways to tackle the same problem in PowerBI.
Start Providing Solutions on the Forum – look for questions that you can answer, and also ones you have to stretch your knowledge to try to resolve. You may not actually get to a solution on these latter ones, but you will learn a ton in the process and when the solution is posted it will be a lot more meaningful to you.
To borrow a quote from Alberto Ferrari, all three of these things are simple but not easy. However, I 100% guarantee if you put in the time and effort to do these three things you will progress quickly in your Power BI skills.
Good luck in your journey!
– Brian