Using a baseline perspective

[ATMAN777]

Hi PowerBI gurus,

Given a dataset example below, I'd like to display total sale quantity by date in a line graph. This I can easily do. I'd like to be able to use a baseline line (the start from which the graph displays totals). This would:

1. Filter sales before the baseline date
2. Only track fruit times present in the baseline date (i.e. excluding sales for new fruit types that occur in future months).

I accomplished this with a hard-coded baseline date by adding a Boolean column "IsInBaseline" where I flag the specified items I want to track from the start month onward meeting the two criteria above.

What I would really like to have is a selectable baseline start date. This can't be a slicer as it would filter data on the page - and it would not include dates beyond the selected date and I still want to meet criteria #2 as well.

Example outputs are in the lower tables

Any ideas?

Output examples:

With a baseline date of 2024/01/01, the totals are by month but EXCLUDE pineapples as these were only introduced in April.

With a baseline of 2024/03/01, totals include 03/01 plus future months but still EXCLUDES pineapples.

Finally, with a baseline of 2024/04/01, values include all fruits including pineapples and tracks them into future months too.

[Selva-Salimi · Accepted solution]

Hi @ATMAN777 

Thanks for updating the post. here is the solution:

you can write a measure as follows:

sales_by_baseline =

var baseline = min(DimDate[date])

var fruits = summarize(filter(all(Table_sales) , Table_sales[Date] = baseline) , Table_sales[Fruit])

RETURN

calculate(sum(Table_sales[Sale Quantity]) , filter (Table_sales,  Table_sales[Fruit] in fruits && Table_sales[Date] >= baseline ))

kepp in mind that X-axis of line chart should be selected from sales_table and the slicer should be selected from DimDate . and also there should NOT be any relation between your sales_table and dimdate

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly. 

[ATMAN777]

Hi @Selva-Salimi,

This is brilliant.  I have so much to learn.   Thank you so much for the solution.

[Selva-Salimi]

Hi @ATMAN777 

would you share example of what you expect?

[ATMAN777]

Hi @Selva-Salimi, 

I have updated the post to include sample output.  

Thanks