Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
I'm putting together a sales forecast. I want to take the accumulated sales up to the moment of the month and divide by the number of working days executed and then multiply by the number of working days missing within the same month. I set up a calendar table with the working days of the month(In this base I mark 1 for working day and 0 for non working day in the WORKING DAYS column fields). The problem is that my database also has data from previous months. Then I'm trying to get the last update date, with it I'll know what month I want to act. I am using the measure
last update =MAX(baseCSV[data]) or last update =LASTDATE(baseCSV[data])
to find the last update of my database which is updated daily. With this function I find the last day and month I want to make the forecast. So I used the measure forecast month = MONTH(last update)
and forecast year=YEAR(last update) I found the month and year I want to calculate. So I tried to calculate how many working days I have within that month with CALCULATE. business day month = CALCULATE(SUM(dCalendar[working days]), dCalendar[number month]=[forecast month]), dCalendar[year]=[forecast year]) and did not work. Can someone help me? I intend to reach this target below.
Could you please provide some sample data and expected results based on this?
Hi,
To your Table/matrix visual, drag Year and Month name from the Calendar Table and write this measure
Work days = sum(dcalendar[working days])
Hope this helps.
User | Count |
---|---|
98 | |
91 | |
84 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |