Forum Discussion

OuluChris's avatar
OuluChris
Helper I
5 years ago
Solved

Relationship between static values and date values (relationships between tables may be needed)

Hi,   I have a table that looks like this:   Name Availability Utilisation Target Cost Rate Chris 1 0.90 100 John 0.5 0.90 125 Peter 0.8 0.70 85   Which when multipli...
Need Help
  • v-robertq-msft's avatar
    v-robertq-msft
    5 years ago

    Hi, @

    According to your description, I think you can use measure and slicer in Power BI to achieve your requirement, you can try this method:

    1. Create a calender table to get the data across 5 years:
    Date = CALENDAR(DATE(2021,1,1),DATE(2025,12,31))
    1. Create these calculated columns in the date table:
    Month = FORMAT([Date],"mmm")
    Month-Year = [Month]&"-"&RIGHT(YEAR([Date]),2)
    Month-Year1 = YEAR([Date])&FORMAT([Date],"mm")
    Is Working Day =

IF(WEEKDAY([Date],2)>5,0,1)

    Then sort the column [Month-Year] like this to make it ordered in the slicer:

     

    1. Create a measure:
    Sum of working days =

SUMX(ALLSELECTED('Date'),[Is Working Day])
    1. Create a slicer to place [Month-Year] and a card chart to place the measure:

    And you can get what you want.

    You can download my test pbix file here

     

    Best Regards,

    Community Support Team _Robert Qin

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

PhilipTreacy's avatar
PhilipTreacy
Super User
5 years ago

Hi Chris,

 

Download sample PBIX file.

 

How are you determing what month it is, and therefore feeding that into your measure?

If you use TODAY for example, you could work out the working days in this month like this (mulitply this by your daily rate to get your desired answer)

Monthly Target Rate = CALCULATE(COUNTROWS('DateTable'), FILTER('DateTable', MONTH('DateTable'[Date]) = MONTH(TODAY()) && YEAR('DateTable'[Date]) = YEAR(TODAY()) && 'DateTable'[IsWorkingDay] = TRUE))

This requires a date table that has a column to indicate if a day is a working day or not.  This requires a list of holidays to be created for the Date Table to use.

The measure above gives 23 working days in Mar 2021 which is correct.

Check my attached PBIX which has a Date Table showing working days.

regards

Phil