Forum Discussion
Using a baseline perspective
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:
- Filter sales before the baseline date
- 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.
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])RETURNcalculate(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 dimdateIf 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.
4 Replies
Hi ATMAN777
would you share example of what you expect?
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])RETURNcalculate(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 dimdateIf 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.
