cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Need help with a DAX formula (sum of quantities between each pair of dates from a list of dates)

Hi everyone,

Could someone assist me with a DAX formula?

Firstly, I have the following data model:

I possess a Date table that enables me to define a date range using a slicer.

I maintain a list of articles, named "Articles," with "cod_art" as the primary key.

I also have a list of sales, named "Sales," which is linked to the "Articles" table via the "cod_art" field, along with two other fields indicating the sales quantity and date.

Additionally, there's a final table named "Article_price_list," detailing price variations for each article. This table is connected to the "Articles" table through the "cod_art" field, providing the article's price and the effective date of that price.

My objective is to create the following matrix:

I can set a date range, and within this range, the matrix will display the effective date for each article. However, what I'm struggling with is creating a DAX formula to calculate the sum of quantities sold for each effective date.

I would greatly appreciate it if someone could offer their assistance with this matter.

Thank you very much!

3 REPLIES 3
Solution Specialist

I would first create a column to get the first date base on the effective date.  (you can probably bring it from the date table but this made it easier)

DATE(Table1[effective Date].[Year],Table1[Effective Date].[MonthNo],1

Then Create something like this
Qty Sold Desc =
VAR currentDate = SELECTEDVALUE(Table1[effective date], BLANK())
VAR NextDate =  CALCULATE(MAX(Table1[effective date]), ALL(Table1[effective date]), Table1[effective date] < currentDate)
Var FirstDayofMonth = SELECTEDVALUE(Table1[firstdate])
RETURN
"Qty Sold Between "&
if(FirstDayofMonth>NextDate,FirstDayofMonth &"-"&currentDate,NextDate&"-"&currentDate)

if you are just looking for just the cumulative qty during that period it be something like this but I didn't see a qty column in your example so i used the price column.

CALCULATE(SUM(Table1[price]),DATESYTD('Table1'[effective date]))

Frequent Visitor

Hello Bmejia,

Actually, I was looking for the first case, but I think I made it a little bit unclear. Instead of the text that you printed, I was looking for the actual value of qty sold. For instance, I need the number 30,000 in the cell labeled "qty sold between 01/Jan - 02/Jan", and the number 2,000 in the cell labeled "qty sold between 02/Jan - 05/Jan", etc.

The "qty" column is located in the "Sales" table, on the left side of the model.

Thank you!

Solution Specialist

Use the measure I provided,but you will need to list the tables where the data is coming as follows

CALCULATE(SUM(Sales[Qty]),DATESYTD('Article_Price_list'[effective date]))