topic Re: Sum by date with condition in DAX Commands and Tips

Sum by date with condition

Anonymous — Tue, 13 Sep 2022 02:48:43 GMT

Hi I am new to power BI and would like to seek for the following advice.

Data:

Item	Sales	Date
Apple	10	01/01/2022
Orange	20	01/01/2022
Lemon	15	01/01/2022
Apple	30	01/02/2022
Orange	20	01/02/2022
Lemon	25	01/02/2022
Apple	10	01/03/2022
Orange	15	01/03/2022
Lemon	10	01/03/2022
Apple	30	01/05/2022
Orange	35	01/05/2022
Lemon	20	01/05/2022

My aim is to have 2 equation.

Equation 1: Sum up the sales (all item) for that month and 2 month after the selected month.

- for example if I select 1 Jan 2022, the result will be sales 1Jan22 - 31Mar22, which is 155

Remarks: the solution come to my mind is to sum up data from start date + 90days, but I am not sure how to make this equation working.

Equaton 2: Same criteria as equation 1 but for specific item

- for example, if I select 1 Jan 2022, the result will be sales for Apple from 1Jan22 - 31Mar22, which is 50

I am currently using a timeline slicer, not sure if it works as timeline covered a period of time, if the above equation can only works on date picker (single date selection), please let me know.

Thank you.

Re: Sum by date with condition

Jihwan_Kim — Tue, 13 Sep 2022 03:21:40 GMT

Hi,

Please check the below picture and the attached pbix file.

Expected measure: = CALCULATE ( SUM ( Data[Sales] ), DATESINPERIOD ( 'Calendar'[Date], LASTDATE ( 'Calendar'[Date] ), 3, MONTH ) )

Only apple expected measure: = CALCULATE ( [Expected measure:], 'Item'[Item] = "Apple" )

Re: Sum by date with condition

Anonymous — Tue, 13 Sep 2022 06:29:55 GMT

Hi Kim,

May you include a month filter as exmaple? I can only show 1 result when a date picked instead of a completed list.

Thank you.

Re: Sum by date with condition

Jihwan_Kim — Tue, 13 Sep 2022 07:57:39 GMT

Hi,

Thank you for your feedback.

If you only need to have a Month Slicer, I suggest creating a Month Column when creating a Calendar table. I added a Month column into a calendar table in the attached file.

Please check the below picture and the attached file whether it suits your requirement.