Skip to main content
cancel
Showing results for 
Search instead 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

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

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.