Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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])))) |
Solved! Go to Solution.
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]))
)
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]))
)
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 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:
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
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.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
125 | |
78 | |
76 | |
58 | |
51 |
User | Count |
---|---|
166 | |
84 | |
68 | |
67 | |
57 |