Calendar Table Percentage of Current Working Days

[martipe1]

I have created a calendar table and merged with a holidays table and working days table (saturdays might be 0.5 working day).

What I want to achieve is to calculate the percentage of actual working days for the past, current, and future months. For example, we are already in August that means that working days for July are 100%, the same for all months in the past, and for August assuming it has 24.5 working days and so far (August 6th) there are 3.5 working days it represents (3.5/24.5) = 14.3% and for September is 0%, the same for all months to come in the future. That percentage I'm going to use it to calculate how good we are doing production compared to our plan.

I was thinking that maybe creating 2 summarized tables might help, but if I try to divide only works if I use and aggregate function like SUM, COUNT, MAX, etc.

These are the two tables I created based on my calendar table:

WorkingDaysperMonth = SUMMARIZE('Calendar','Calendar'[YearMonth],"Working Days", SUM('Calendar'[WeekDays.Value]))

CurrentWorkingDays = SUMMARIZE(FILTER('Calendar','Calendar'[Date]< TODAY()),'Calendar'[YearMonth],"Working Days", SUM('Calendar'[WeekDays.Value]))

WeekDays.Value is 0 for non working day and 1 or 0.5 for a working day

I created a relationship one to one by YearMonth with both cross filter direction

Any suggestion will be deeply apreciated

[Shravan133 · Accepted solution]

To ensure the percentage value varies correctly in your Matrix visual, we need to ensure that the calculation is context-aware and changes dynamically based on the row context (e.g., YearMonth).

Here’s a refined approach with a detailed breakdown:

Step 1: Ensure Context-Aware Measures

1. Total Working Days per Month

TotalWorkingDaysPerMonth =

CALCULATE(

    SUM('Calendar'[WeekDays.Value]),

    ALLEXCEPT('Calendar', 'Calendar'[YearMonth])

)

1. Working Days Till Today

WorkingDaysTillToday =

CALCULATE(

    SUM('Calendar'[WeekDays.Value]),

    'Calendar'[Date] <= TODAY(),

    ALLEXCEPT('Calendar', 'Calendar'[YearMonth])

)

1. Percentage Working Days (Adjusted for Current Month)

This measure ensures the percentage is correctly calculated for the current, past, and future months:

PercentageWorkingDaysCurrentMonth =

IF(

    MAX('Calendar'[YearMonth]) = YEAR(TODAY())*100 + MONTH(TODAY()),

    DIVIDE(

        [WorkingDaysTillToday],

        [TotalWorkingDaysPerMonth],

        0

    ),

    IF(

        MAX('Calendar'[YearMonth]) < YEAR(TODAY())*100 + MONTH(TODAY()),

        1,

        0

    )

)

Step 2: Use the Measures in a Calculation

Now, use the percentage measure to calculate your final metric. Here’s how you can set it up:

1. Actual

Assuming you already have a measure for Actual:

Actual = SUM('YourTable'[Actual])

1. Plan

Assuming you already have a measure for Plan:

Plan = SUM('YourTable'[Plan])

1. Adjusted Plan

Adjust the Plan using the Percentage Working Days measure:

AdjustedPlan = [Plan] * [PercentageWorkingDaysCurrentMonth]

1. Final Calculation

Calculate the final percentage:

FinalCalculation = DIVIDE(

    [Actual],

    [AdjustedPlan],

    0

) * 100

Step 3: Use in Matrix Visual

In your Matrix visual, use the measures as follows:

• Rows: YearMonth (or any other relevant dimension)
• Values: Actual, Plan, AdjustedPlan, FinalCalculation

Ensure that the context is correctly maintained by the YearMonth field in the Rows section of the Matrix visual. This will ensure the percentage values change dynamically based on the row context.

Troubleshooting

If the percentage measure is still the same for all rows, double-check the following:

1. Data Model Relationships: Ensure that the relationships between your tables are correctly set up, particularly the relationship between your fact table and the Calendar table.
2. Row Context: Verify that the YearMonth field in the Matrix visual’s Rows section is correctly affecting the row context.
3. Measure Dependencies: Ensure that each measure is correctly referencing the context-aware fields and is recalculating based on the row context.

Example of Measures in Matrix

Here’s a final example setup for your Matrix visual:

1. Rows: YearMonth from the Calendar table
2. Values:
• Actual
• Plan
• AdjustedPlan
• FinalCalculation

This setup ensures that your percentage measure reflects the correct value based on the month and dynamically adjusts based on the context provided by the Matrix visual’s rows.

[martipe1]

Thank you very much for your answer.

Sorry for my late reply but not feeling good, I think it works.

Thanks!!

[Shravan133]

To achieve the calculation of the percentage of actual working days for the past, current, and future months, you can create measures in Power BI rather than relying on summarized tables. This approach will give you more flexibility and ensure the calculations are dynamic based on the current date.

Here’s a step-by-step solution using DAX measures:

Step 1: Create a Calendar Table

Ensure you have a Calendar table with at least the following columns:

• Date
• YearMonth (formatted as YYYYMM)
• WeekDays.Value (1 for working day, 0 or 0.5 for non-working day)

Step 2: Create Measures

1. Total Working Days per Month

TotalWorkingDaysPerMonth =

CALCULATE(

    SUM('Calendar'[WeekDays.Value]),

    ALLEXCEPT('Calendar', 'Calendar'[YearMonth])

)

1. Current Working Days

CurrentWorkingDays =

CALCULATE(

    SUM('Calendar'[WeekDays.Value]),

    'Calendar'[Date] <= TODAY()

)

1. Percentage of Actual Working Days

PercentageActualWorkingDays =

DIVIDE(

    [CurrentWorkingDays],

    [TotalWorkingDaysPerMonth],

    0

)

Step 3: Calculate Current and Future Working Days Separately

To handle the calculation of current and future working days separately, create two additional measures:

1. Working Days Till Today

WorkingDaysTillToday =

CALCULATE(

    SUM('Calendar'[WeekDays.Value]),

    'Calendar'[Date] <= TODAY(),

    ALLEXCEPT('Calendar', 'Calendar'[YearMonth])

)

1. Total Working Days for Current Month

TotalWorkingDaysCurrentMonth =

CALCULATE(

    SUM('Calendar'[WeekDays.Value]),

    ALLEXCEPT('Calendar', 'Calendar'[YearMonth])

)

Step 4: Percentage Calculation for Current Month

PercentageWorkingDaysCurrentMonth =

IF(

    MAX('Calendar'[YearMonth]) = YEAR(TODAY())*100 + MONTH(TODAY()),

    DIVIDE(

        [WorkingDaysTillToday],

        [TotalWorkingDaysCurrentMonth],

        0

    ),

    IF(

        MAX('Calendar'[YearMonth]) < YEAR(TODAY())*100 + MONTH(TODAY()),

        1,

        0

    )

)

Step 5: Use the Measures in Visuals

You can now use these measures in your visuals to show the percentage of actual working days for past, current, and future months. Here’s an example visualization idea:

1. Line Chart
• Axis: YearMonth
• Values: PercentageWorkingDaysCurrentMonth
2. Table
• Columns: YearMonth, PercentageWorkingDaysCurrentMonth

Summary

The key idea is to use DAX measures to dynamically calculate the working days based on the current date and the month being considered. This approach ensures your calculations are always up-to-date and correctly reflect the current progress within the month.

You can further refine these measures based on your specific needs, such as accounting for holidays or partial working days. This setup should provide a robust foundation for tracking and comparing your production against your plan.

[martipe1]

Thank you vert much for your answer.

I did create the above mentioned measures, for my visual I'm using a Matrix. In the end the calculation I want to achieve shoud be (Actual/(Plan* Your Percentage measure)) * 100.

I'm doing something wrong as when I put your percentage measure in my matrix visual this is the result:

 Thanks in advance for your help