Forum Discussion

AL_Thomas's avatar
AL_Thomas
Frequent Visitor
1 year ago
Solved

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]))))
Need Help
  • AL_Thomas's avatar
    AL_Thomas
    1 year ago

    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]))

    )

5 Replies

  • quantumudit's avatar
    quantumudit
    Super User
    1 year ago

    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

  • AL_Thomas's avatar
    AL_Thomas
    Frequent Visitor
    1 year ago

    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]))))

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi AL_Thomas ,

       

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

      It's working fine.

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

      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



      • AL_Thomas's avatar
        AL_Thomas
        Frequent Visitor
        1 year ago

        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.

  • AL_Thomas's avatar
    AL_Thomas
    Frequent Visitor
    1 year ago

    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]))

    )