topic Re: Filter values in each column based on particular day in DAX Commands and Tips

Filter values in each column based on particular day

Anonymous — Thu, 03 Sep 2020 05:06:51 GMT

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?

Re: Filter values in each column based on particular day

Greg_Deckler — Thu, 03 Sep 2020 04:19:42 GMT

Anonymous - 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.

Re: Filter values in each column based on particular day

amitchandak — Thu, 03 Sep 2020 04:23:18 GMT

Anonymous , 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))

Re: Filter values in each column based on particular day

Anonymous — Thu, 03 Sep 2020 04:52:40 GMT

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.

Re: Filter values in each column based on particular day

Anonymous — Thu, 03 Sep 2020 04:54:00 GMT

amitchandak I'm looking for the sum of amount for each year based on the day the campaign is running.

Re: Filter values in each column based on particular day

Greg_Deckler — Thu, 03 Sep 2020 05:10:24 GMT

Anonymous - 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