Forum Discussion

BI_user2023's avatar
BI_user2023
Frequent Visitor
3 years ago

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!

Need Help

3 Replies

  • Bmejia's avatar
    Bmejia
    Icon for Super User rankSuper User
    3 years ago

    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's avatar
      BI_user2023
      Frequent Visitor
      3 years ago

      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's avatar
        Bmejia
        Icon for Super User rankSuper User
        3 years ago

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