Forum Discussion

OuluChris's avatar
OuluChris
Icon for Helper I rankHelper 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.

v-robertq-msft's avatar
v-robertq-msft
Icon for Community Support rankCommunity Support
5 years ago

Hi, OuluChris 

According to your description and sample data, your data table doesn’t have any date column to link to the ‘Date’ table. So what’s the expected relationship between your data table and the calendar table?

 

If you want to get the number of work days within each month, PhilipTreacy has posted the solution. If you want to suppress this warning, I think give a date column to your data table is very necessary.

You can post your sample pbix file or some sample data(without sensitive data) and your expected result so that we can help you in advance.

Thank you very much!

How to Get Your Question Answered Quickly 

 

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.