Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
BI_user2023
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:

BI_user2023_1-1692265936206.png

 

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:

BI_user2023_0-1692348677360.png

 

 

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
Bmejia
Super User
Super User

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

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!

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

Helpful resources

Announcements
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

Find out what's new and trending in the Fabric community.