How can I have total of last year or next year budget while keeping MTD and YTD (which are relating to slicers)

Hi Team,
I have date slicers as follows and my report include MTD, YTD and I will have to add total next year budget which is not relating to FP but it has to be related to FY.
Is there any video for those together or is there anyone calculating in that way? if it is, how did you calculate?
For example, in my slide, FY 21 Bud (which is total FY 21 Budget) should be different to YTD NY Bud (which is YTD April 2020)

In a different note, is there any way to highlight particular row? in my case, I would like to highlight Net Result row (which is not actual total, instead I calculated in SQL to get the Net Result.
Many thanks.
Regards,
ammu

Please check out the thread below, which I think will address your row formatting question

With regard to your other question, the answer I think will depend on what your data model looks like. If you could please post your PBIX file, that would allow us to provide a specific solution. If you have any confidential data in your file, here are some quick and easy techniques to use in power query to mask sensitive data:

I hope this is helpful.

  • Brian

Hi Brian,
Thanks for replying so promptly.
My Level1 column is not a measure. It is purely a column from the table. So I cannot use
SWITCH( TRUE(), ‘Financial Categories’[Financial Statement List] as it has to be a measure after TRUE(),?
Is there anyway I can use the column and item in the column instead of a measure.

I will try to mask my data in PBIX as you suggested. There is a similar question I found in Power BI forum. but the solution does not work for me.
Thank you.
Regards,
ammu

##

Report Show Both YTD Budget and Full Yr Budget

‎12-28-2017 12:05 PM

Afternoon


I’m fairly new to Power BI and Dax but was wondering if anyone knew how to show the Budget both as of YTD and Full Year while the report is using slicers?


***My request is to show Actual vs Budget and Full Year Budget minus Actual to show Year to Go. ***


***I am am also using slicers for each month of the calendar year so that my end users may also do Month to Date. ***


***I have no problem with the Actual vs Budget YTD it’s just can I also have Full Yr Budget? My Budget is by product level. ***


Thanks in Advance

Message 1 of 2

1,370 Views

0

Reply

1 REPLY

v-sihou-msft

Microsoft v-sihou-msft

Microsoft

##

Re: Report Show Both YTD Budget and Full Yr Budget

‎01-03-2018 11:40 PM

@IamTDR


***To get the Full Year Budget based on the selection in slicer, you can write a measure like below: ***


Measure = CALCULATE(SUM(‘Internet Sales’[Sales Amount]),ALLSELECTED(‘Date’[Calendar Year]))

Regards,

Absolutely. There are two ways you can do this:

  1. if you make Highlight (which includes the SWITCH( TRUE()) statement) a calculated column, you can use your Financial Categories’[Financial Statement List] column as is with no problem, since a calculated column has defined row context that allows it to narrow your column entry to a single value for each row.
  2. you also write a Highlight measure. In your switch statement, you will need to wrap your column entry in a SELECTEDVALUE() function to tell the statement to use the proper scalar value from the column as defined by the evaluation context.

In the attached solution, I used the first approach, but the second will work equally well and in most cases, a measure will be preferable to a calculated column anyway.

If the above explanation is not entirely clear to you, I would strongly recommend viewing the following videos, as fully understanding evaluation, row and filter context is the most important concept in all of DAX.

I hope this is helpful to you.

Hi @ammu, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks!