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.

OuluChris's avatar
OuluChris
Helper I
5 years ago

Thanks for the replies everybody. I see now I wasn't as clear as I should have been in describing the problem.

 

So I have the table I showed previously:

NameAvailabilityUtilisation TargetCost Rate
Chris10.90100
John0.50.90125
Peter0.80.7085

 

And I also have a date table that looks like this:

DateYearMonth NumberMonth-YearMonth-Year SortIs Working Day?
01/01/202120211Jan-2120210010
02/01/202120211Jan-2120210010
03/01/202120211Jan-2120210010
04/01/202120211Jan-2120210011
     FORMULA

 

At the bottom of this table (which includes five years worth of dates) is the formula:

Available Working Days:=SUM([Is Working Day?])

 

This then allows me to figure out the total number of working days in a pivot table by selecting the Month-Year from the filter (or using the month-year in other ways but it's the filter I'm mostly interested in).

 

This all works but generates the warning message I referred to previously.

 

I hope this makes it a bit clearer.

 

Thanks a lot.

 

 

Chris