Running Total Issue in Power BI

Question

Hi,I have one table where I have worker start date and end date. This table has unique values only. So for each worker there is one start date and one end date.&nbsp;The start date of the worker is connected to the Calender Table.I am trying to show count of workers such that eg.if Italy had 3 workers in Jan and one worker was added in April then the output should be likeJan 3Feb 3March 3April 4&nbsp;I am using below formula to achieve thisVAR CurrentDate = MAX('Calender'[Date])RETURN&nbsp; &nbsp;&nbsp;CALCULATE(&nbsp; &nbsp; &nbsp; &nbsp;&nbsp;DISTINCTCOUNT('Worker Table[ID]),&nbsp; &nbsp; &nbsp; &nbsp; 'Worker Table'[GEOGRAPHY]&nbsp; &nbsp; &nbsp; &nbsp; IN { "Poland", "Spain", "France", "Australia", "Italy" },&nbsp; &nbsp; &nbsp; &nbsp; 'Worker Table'[Active/Ended] = "Active",&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp;FILTER(&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;ALLSELECTED('Calender'[Date]),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 'Calender'[Date] &lt;= CurrentDate&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;&nbsp; &nbsp; &nbsp; &nbsp; ))&nbsp;but this is not showing running total, It is showing me count directly in the month the contractor was added.Result wasJan 3Feb 0&nbsp;March 0April 4&nbsp;How to solve this, is this because we have distinct start dates in the Worker table?

anonymous · Answer

Hi&nbsp;Revati25,
&nbsp;
Thank you for reaching out to the Microsoft Fabric Forum Community, and special thanks to&nbsp;Natarajan_M&nbsp;,&nbsp;Jihwan_Kim&nbsp;and&nbsp;cengizhanarslan&nbsp; for prompt and helpful responses.
Just following up to see if the Response provided by community members were helpful in addressing the issue. if the issue still persists Feel free to reach out if you need any further clarification or assistance.
&nbsp;
Best regards,Prasanna Kumar
&nbsp;

cengizhanarslan · Answer

Please try the measure below:
Measure =
VAR CurrentDate = MAX ( 'Calendar'[Date] )
RETURN
CALCULATE (
    DISTINCTCOUNT ( 'Worker Table'[ID] ),
    REMOVEFILTERS ( 'Calendar' ),   -- important: removes the StartDate relationship filtering
    'Worker Table'[GEOGRAPHY] IN { "Poland", "Spain", "France", "Australia", "Italy" },
    FILTER (
        ALL ( 'Worker Table' ),
           'Worker Table'[StartDate] &lt;= CurrentDate
            &amp;&amp; ( ISBLANK ( 'Worker Table'[EndDate] ) || 'Worker Table'[EndDate] &gt; CurrentDate )
    )
)

jihwan_kim · Answer

Hi,
Please correct me if my sample pbix file is not similar as what you have, but it seems like your dax measure works.
Please check the below and the attached pbix file.
&nbsp;
&nbsp;
Count of workers: = 
VAR CurrentDate =
    MAX ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Worker Table'[ID] ),
        'Worker Table'[GEOGRAPHY]
            IN { "Poland", "Spain", "France", "Australia", "Italy" },
        'Worker Table'[Active/Ended] = "Active",
        FILTER ( ALLSELECTED ( 'Calendar'[Date] ), 'Calendar'[Date] &lt;= CurrentDate )
    )
&nbsp;

natarajan_m · Answer

Hi&nbsp;Revati25&nbsp;, I was able to recreate your scenario I created 2 measures Base measure and final measure&nbsp;Headcount Base =VAR CurrentDate = MAX('Calendar'[Date]) VAR PeriodStart = MIN('Calendar'[Date])RETURNCALCULATE(&nbsp; &nbsp; DISTINCTCOUNT('Fact Table'[ID]), -- Counts unique employees&nbsp; &nbsp; 'Fact Table'[Start] &lt;= CurrentDate, &nbsp; &nbsp; OR(&nbsp; &nbsp; &nbsp; &nbsp; ISBLANK('Fact Table'[End]), &nbsp; &nbsp; &nbsp; &nbsp; 'Fact Table'[End] &gt;= PeriodStart&nbsp; &nbsp; ))Headcount Final Measure =VAR CurrentDate = MAX('Calendar'[Date])RETURNCALCULATE(&nbsp; &nbsp; -- 1. Call your existing base measure&nbsp; &nbsp; [Headcount Base], &nbsp; &nbsp; &nbsp; &nbsp; -- 2. Expand the window to include all dates up to the current date&nbsp; &nbsp; FILTER(&nbsp; &nbsp; &nbsp; &nbsp; ALL('Calendar'),&nbsp; &nbsp; &nbsp; &nbsp; 'Calendar'[Date] &lt;= CurrentDate&nbsp; &nbsp; ))Italy:Italy :Data :&nbsp;Model :Thanks&nbsp;If this response was helpful in any way, I’d gladly accept a kudo.Please mark it as the correct solution. It helps other community members find their way faster.&nbsp;

revati25 · Answer

I have data with start date and end date as well, I want to show employee active only till its end date is not ended.
Eg, 1st row
Start Date is September 2021 and End Date February 2024. So this employee should be active from
2021 September to February 2024.

Relationship is between - > Start Date and Calendar Table

Forum Discussion

Running Total Issue in Power BI

8 Replies

Related content

Tableau to PowerBI Running Totals Issue

Running Totals

Running total

running total performance issues

Issues running Spark Notebook