Forum Discussion
Sum by date with condition
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.
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.
3 Replies
- Jihwan_Kim
Super User
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" )- Jihwan_Kim
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.