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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
AL_Thomas
Frequent Visitor

DatesYTD inside a Calculate and Filter function, How?

Hi all,

   I have created a variable to calculate totals based on selectedvalue. I am new to Power BI, can you tell me what i am doing wrong here in the formula? I am trying to calcuate YTD totals for the selectedvalue items. The datesytd functions works on my other formulas, but it's not calculating the ytd totals here. Is my syntax correct? I need to use the variable format because i have a template i uploaded to Power BI and using switch function to summarize the totals for each row (calculated_Category) from the template.

 

VAR CurrentItem = SELECTEDVALUE('table'[Field])

Return

CALCULATE([Totals], FILTER('table','[Calculated_Category] = CurrentItem),DATESYTD(Calender[Date]))))
1 ACCEPTED SOLUTION
AL_Thomas
Frequent Visitor

I think i may have solved the issued by using the calculate inside another calculate function.

 

SelectedBalance YTD =

VAR CurrentItem = SELECTEDVALUE(IncomeStatement Dtls)

 

RETURN

SWITCH(

    TRUE(),

    CurrentItem = "Net Sales", [Net Sales YTD],

    CurrentItem = "Cost of Sales", [Cost of Sales YTD],

    CurrentItem = "Standard Margin", [Std Margin YTD],

   

    CALCULATE(CALCULATE(

        [Totals],

        FILTER(

            'Income Statement Dtls',

            'Income Statement Dtls'[Category] = CurrentItem

        )

    ),DATESYTD(Calender[Date]))

)

View solution in original post

5 REPLIES 5
AL_Thomas
Frequent Visitor

I think i may have solved the issued by using the calculate inside another calculate function.

 

SelectedBalance YTD =

VAR CurrentItem = SELECTEDVALUE(IncomeStatement Dtls)

 

RETURN

SWITCH(

    TRUE(),

    CurrentItem = "Net Sales", [Net Sales YTD],

    CurrentItem = "Cost of Sales", [Cost of Sales YTD],

    CurrentItem = "Standard Margin", [Std Margin YTD],

   

    CALCULATE(CALCULATE(

        [Totals],

        FILTER(

            'Income Statement Dtls',

            'Income Statement Dtls'[Category] = CurrentItem

        )

    ),DATESYTD(Calender[Date]))

)

AL_Thomas
Frequent Visitor

I am trying to create an P&L statement. I have all the current calculations working fine. When i trying to do the YTD calculation, it does not compute YTD but just shows the current totals. 

I have an income statement template i uploaded to power bi. I then created totals for the subtotals in the template using a variable and switch function. At the end of the switch function, if my the selectedvalue is not one of the subtotals, then i want to sum up the totals YTD for the selected row.

  • the "CurrentItem" is the selected row on the tempate.
  • the switch statement calls for calculations of subtotals (ex. net sales, standard margin, net income after tax)
  • if it's not a subtotal, then i want to go into my table and sum up the total for the "currentItem" using DatesYTD.   (if the currentitem is blank, then give me blank else calculate ytd total for the current row)

The switch statement works, but the IF statement at the bottom does not calculate YTD but just calculate for the current month selected via slicer.

 

SelectedBalance YTD =

VAR CurrentItem = SELECTEDVALUE('P&L Template' [Rows for template])

 

RETURN

SWITCH(

    TRUE(),

    CurrentItem = "Net Sales",[Net Sales YTD],

    CurrentItem = "Standard Margin",[Std Margin YTD],

    CurrentItem = "Total Operating Expenses",[Total Operating Expenses YTD],

    CurrentItem = "Operating Income", [Operating Income YTD],

    CurrentItem = "Net Income after Tax",[Net Income YTD],

 

    IF(CurrentItem = BLANK(), BLANK(),

    CALCULATE([Totals], FILTER('IncomeStatement Dtls','IncomeStatement Dtls'[Calculated_IS_Category] = CurrentItem), DATESYTD(Calender[Date]))))

Hi @AL_Thomas ,

 

Are you using Year, Month fields from Canlendar in your visual?
I made a simple model:

vcgaomsft_1-1735197863984.png

It's working fine.

vcgaomsft_0-1735197844971.png

However, using the year and month fields from the IncomeStatement Dtls table returns unintended values.

vcgaomsft_2-1735197964251.png

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum



I am using the calender table created seperately. In my model, the year and month are on a slicer rather than on the table. When i filter the slicer for a month, i want to see all the totals for the year upto that month.

quantumudit
Super User
Super User

Hello @AL_Thomas 

It would be helpful if you could provide some details on the data model and some sample data. It is a bit challenging to provide accurate DAX without understanding the context.

 

Thanks,

Udit

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.