topic Re: A measure that calculates sales based on a table with bonuses in DAX Commands and Tips

A measure that calculates sales based on a table with bonuses

IrekK — Wed, 04 Jan 2023 20:49:54 GMT

Hi, I can't figure out a metric that will calculate the sold quantity of the products I have in the "Bonus Products" table.
The bonus products table contains articles that are selected in monthly cycles, but it happens that the action for a given product may last several days. Hence the Start and End date.
In addition, it may happen that the same product in one week of the month may be priced at $10 and the next week its price will be $15.
Too difficult a task for my strength.
Help me find a solution, hints.
Thank you in advance

Re: A measure that calculates sales based on a table with bonuses

Anonymous — Thu, 05 Jan 2023 01:48:50 GMT

Hi IrekK

Based on the information i have got, i make a flow chart you can refer to

and can you provide some sample data that i can provide more solution for you.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: A measure that calculates sales based on a table with bonuses

IrekK — Sat, 07 Jan 2023 12:49:29 GMT

hello Yolo Zhu, thanks for your willingness to help. please write in what form I can post sample data here, generally I have a sample prepared in pbx.

Re: A measure that calculates sales based on a table with bonuses

Anonymous — Mon, 09 Jan 2023 01:17:41 GMT

Hi IrekK

You can provide picture of sample data or you can put your pbix file to your onedrive, then share the link here.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: A measure that calculates sales based on a table with bonuses

IrekK — Mon, 09 Jan 2023 20:55:27 GMT

Hi, finally I was able to put the file in onedrive.

https://1drv.ms/u/s!AvSMwqcJvxJvgZQEctRS-wtP5iO0pA?e=bMm4l4

Re: A measure that calculates sales based on a table with bonuses

Anonymous — Tue, 10 Jan 2023 08:24:18 GMT

Hi IrekK

Based on my understanding of the information you have provided , I create a measure to calculate total quantities

Total_salequantity = var _startdate=DATE(MAX(BONUS[Year]),MAX(BONUS[Month]),RIGHT(MAX(BONUS[DimDateStart]),2)) var _enddate=DATE(MAX(BONUS[Year]),MAX(BONUS[Month]),RIGHT(MAX(BONUS[DimDateEnd]),2)) return CALCULATE(SUM(SALES[Quantity]),DATESBETWEEN('CALENDAR'[Date],_startdate,_enddate))

and I change the cross filter between Bonus table and Article table to 'Both'

Output:

If the information above cannot meet your requirement, can you provide more information? such as What do you want to calculate, total sales or toal quantities. from the information you have offered, you want to calculate total quantities.

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.