topic Re: Running Totals Measure question in DAX Commands and Tips

Running Totals Measure question

westman — Tue, 23 May 2023 19:37:03 GMT

I am trying to figure out running totals. I have a matrix that has the previous years running total by month and the current year. The aggregate correctly, however in the cells in the table under current year, the PY(previous year) YTD (year to date) total is displayed for each month in the CY(current year). I would like to blank out those fieds in the matrix

Here is the dax measure i wrote to calculate the running totals in 20222, and then copied for another measure and adjusted for 2023

Running Total Labor 2022 =

CALCULATE(

SUM('Factory Payroll'[Grand Total Labor]),

FILTER(

ALL('Factory Payroll'),

YEAR('Factory Payroll'[Pay Date]) = 2022

&& 'Factory Payroll'[Pay Date] <= MAX('Factory Payroll'[Pay Date])

)

I would like to blank out the area circled above.

Thank you for your assistance in advance

Any links to pertaining to the topic at hand would be appreciated.

Drew

Re: Running Totals Measure question

Jihwan_Kim — Wed, 24 May 2023 03:36:48 GMT

Hi,

I am not sure how your datamodel looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

I hope the below can provide some ideas on how to create a solution for your datamodel.

Re: Running Totals Measure question

westman — Wed, 24 May 2023 15:57:38 GMT

Jihwan_Kim Thank you for your assistance..
Apologies im relatively new to Power bi and dax

I formatted your measure below as:

RT labor 2023 =

Var _condition =

Max('Datestbl'[Year] ) = 2023

Return

IF(

Not ISBLANK([Total Payroll Wages] ),

CALCULATE( [Total Payroll Wages], DATESYTD( 'Datestbl'[Date] ))

Divide ( _condition, _condition )

)

However, it returned zero data when applying it to the matrix visual. just blank all blank rows.

Please see attached pic of datamodel and measure output

Thanks

westman

Re: Running Totals Measure question

Jihwan_Kim — Thu, 25 May 2023 03:01:35 GMT

Hi,

Please share your sample pbix file's link, and then I can try to look into it.

Thank you.

Re: Running Totals Measure question

westman — Thu, 25 May 2023 16:13:20 GMT

I am unable to attach files here as a newer user. I sent a pm to you with a one drive link to the file.

I appreciate your assistance.

westman

Re: Running Totals Measure question

Jihwan_Kim — Fri, 26 May 2023 03:26:42 GMT

Hi,

Thank you for your message.

I think you can try using date column from Datestbl table, not from Factory Payroll table.

Thank you.