topic Re: DAX measure CALCULATE budget context in DAX Commands and Tips

DAX measure CALCULATE budget context

geirselvag — Mon, 18 Dec 2023 08:04:06 GMT

Hello,

I'm looking for the correct DAX measure of how to return the correct budget by having following condition.

One slicer "Report Period" always required.
Relevant budget need to be returned, based on selected period.

This is an example of the report period and expected corresponding budget value.

...

I'm grateful for any tips on how I can get this PowerBI DAX measure to work, on these expected conditions.

Re: DAX measure CALCULATE budget context

Anonymous — Mon, 18 Dec 2023 01:29:30 GMT

Hi geirselvag ,

The data you gave is not comprehensive enough, I hope you can explain to me your desired output. For example, the data for 2023-03 is clearly only PRO 10,how did you get PRO 11? And why does Current value exist in baseline of 2023-06?

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards
Community Support Team _ Rongtie

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: DAX measure CALCULATE budget context

geirselvag — Mon, 18 Dec 2023 08:04:50 GMT

Hi Anonymous,

Thank you for reaching out for assistance, I appreciate that.

Detailed explaination of desired output.

The process for the projects is that they can have multiple re-baselines during it's lifetime. Example when a project is initiated, we get two datasets "Current" in one table 'CurrentBudget' and copy of this dataset as "CCE1" to another table 'Baseline'. When there is no re-baseline (e.g only "CCE1"), the desired output will always be "Current" or first baseline regardless of what reporting period is selected.

When creating first baseline ("2023-01") for PRO10, we have two same datasets ("CCE1"="Current") until next re-baseline is being created. Store all previous baseline's with period "yyyy-mm" and baseline "CCEx" as reference. Since there are two baseline (e.g "Current" <> "CCE1"), this mean desired output when select report period "2023-01", "2023-02", "2023-03", "2023-04", "2023-05" will then be "CCE1" from first baseline with the value 10,000.

When report period "2023-06" is selected or after the desired output is "Current" (ref. "CCE2") with value 15,000 . After the last re-baseline (e.g "2023-06", "CCE2"), we always want desired output to be "Current" which is the same/copy to "Baselineof "CCE2".

The project "PRO11" has only one baseline, therefore from "2023-03" the desired output of this project is "Current" from "2023-03" to "2023-12". The table 'Baseline' includes baseline for several projects with the same process.

Hopefully, this is useful information on how we need desired output to be. Thank you for any tips of how to make this possible in a Power BI DAX measure.