Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
westman
Regular Visitor

Running Totals Measure question

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])
    )
)RTexample.PNG
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
5 REPLIES 5
Jihwan_Kim
Super User
Super User

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.

 

Jihwan_Kim_0-1684898743893.png

 

Jihwan_Kim_1-1684899373883.png

 

 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


@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 outputRT2023.PNGRTdatamodel.PNG
 
Thanks
westman

Hi, 

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

Thank you.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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

Hi,

Thank you for your message.

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

Thank you.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.