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

[BI_user2023]

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!

[Bmejia]

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]))

[BI_user2023]

Hello Bmejia,

Thank you for your response!

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.

Could you please help me with this?

Thank you!

[Bmejia]

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]))