Forum Discussion

RobRayborn's avatar
RobRayborn
Icon for Helper IV rankHelper IV
3 years ago

Linking a Monthly Calendar

I need to utilize an amount of work days per month that is asigned by a sperate table.  This seperate table (WorkDaysbyMonthandYear) has the calendar dates with first day of each month.  I can link this to my full fDate table (with offsets).  The WorkDaysbyMonthandYear table has the amount of workdays per month/year for 5 seperate Locations.
So, 01/01/2023, Column Location 1 = 21 (days), Column 2 Location = 20 (days)......
Then 2/1/2023, Column Location 1 = 19 (days), Column 2 Location = 19 (days)...
Using DAX how can I calculate the Amount of workdays remaining thoughout the calendar month/year based off of the last date with data (Sales). 
I would like to know how to do this with DAX. I've seen a Youtube video showing this but cannot remeber Instructor.
I can most likely do this task by pulling the number of days per month/year into my fDate table, but that then bloats my fDate table a bit.  
Suggestions?

Need Help
Tutorial Requests

1 Reply

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    3 years ago

    RobRayborn Well, first I would unpivot the Location columns. And assuming you unpivot those columns to get a column called "Location" and a column called "Value" in Power Query Editor. Then it is a matter of how you are going to use this as a measure or a column and what is going to be in the visual (we will assume Location column from this table is in visual). But, assuming that this table is not related to your fDate table you could write a measure like this:

     

    Measure =
  VAR __Date = MAX('Sales'[Date])
  VAR __Table = FILTER('WorkDaysbyMonthandYear', [Date] > __Date)
  VAR __Result = SUMX( __Table, [Value])
RETURN
  __Result