Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a scenario where data for the year 2018, 2019 are given and new data gets uploaded everyday for 2020. Let’s say I have a column called as ‘campaign day’ which holds values from 1 to 31. Amount and other details for the year 2018 and 2019 for each campaign day is already present and the value for 2020 gets loaded as each campaign day passes. This means that the data for campaign day 1 in the year 2020 comes as each campaign day passes. Now, I want to calculate the sum of amount received in each campaign based on each campaign day for all the three years across different regions. For example, if today is the 1st day of campaign, so data for the year 2020 would have been uploaded in the database and it should show the total sum of amount in each region for the 1st day for 2018,2019 and 2020 (even though I have all the data for 2018 and 2019). Next, when the 2nd day arrives, it should calculate the sum of amount in each region for the 2nd day for 2018, 2019 and 2020 and so on for remaining campaign days. Hence, it should calculate year to date value based on campaign day.
This is how my original data would look like:
Region | Campaign Day | Amount | Year |
East | 1 | 25 | 2018 |
East | 2 | 20 | 2018 |
East | 3 | 30 | 2018 |
East | 4 | 15 | 2018 |
East | 5 | 50 | 2018 |
West | 1 | 30 | 2018 |
West | 2 | 35 | 2018 |
West | 3 | 52 | 2018 |
West | 4 | 48 | 2018 |
West | 5 | 60 | 2018 |
East | 1 | 32 | 2019 |
East | 2 | 34 | 2019 |
East | 3 | 45 | 2019 |
East | 4 | 65 | 2019 |
East | 5 | 47 | 2019 |
West | 1 | 54 | 2019 |
West | 2 | 49 | 2019 |
West | 3 | 60 | 2019 |
West | 4 | 76 | 2019 |
West | 5 | 78 | 2019 |
East | 1 | 34 | 2020 |
East | 2 | 39 | 2020 |
East | 3 | 2020 | |
East | 4 | 2020 | |
East | 5 | 2020 | |
West | 1 | 42 | 2020 |
West | 2 | 50 | 2020 |
West | 3 | 2020 | |
West | 4 | 2020 | |
West | 5 | 2020 |
If today is the 1st day of my campaign, this is how my output should like:
Region | 2018 | 2019 | 2020 |
East | 25 | 32 | 34 |
West | 30 | 54 | 42 |
and when the data for 2020 automatically refreshes for 2nd day of campaign, my output should look like:
Region | 2018 | 2019 | 2020 |
East | 20 | 34 | 39 |
West | 35 | 49 | 50 |
How can I do this in powerbi?
@jazzk , Prefer to a separate table for Campaign Day
Try measures like
Campaign value =
var _max = maxX(allselected('Campaign'), 'Campaign'[Campaign Day])
return
CALCULATE(sum('order'[Qty]),filter(ALL('Campaign'),'Campaign'[Campaign Day]=_max))
Campaign value =
var _max = maxX(allselected('Campaign'), 'Campaign'[Campaign Day])
return
CALCULATE(sum('order'[Qty]),filter(ALLSelected('Campaign'),'Campaign '[Campaign Day]=_max))
Campaign value =
var _max = maxX(allselected('Campaign'), 'Campaign'[Campaign Day])
return
CALCULATE(sum('order'[Qty]),filter('Campaign ','Campaign'[Campaign Day]=_max))
@amitchandak I'm looking for the sum of amount for each year based on the day the campaign is running.
@jazzk - Seems like a Running Total quick measure? Put DayAmount in your visual, click the little drop down area in the Fields area, choose Quick Measure, choose Running Total, and use Campaign Day for your "date" essentially.
@Greg_Deckler I don't think a running total would provide me the expected output as I want the sum of amount to be updated based on the campaign day as mentioned in the question.
@jazzk - Ahh, you want Lookup Min/Max then, a value as of the "latest", "maximum", "minimum" of something. https://community.powerbi.com/t5/Quick-Measures-Gallery/Lookup-Min-Max/m-p/985814#M434
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
25 | |
19 | |
14 | |
10 |
User | Count |
---|---|
57 | |
49 | |
44 | |
18 | |
18 |