CODEWITHSUNDEEP
dax
Glen Højer
Glen Højer

Reputation: 1

DAX: Calculate sum from one table based on dates in another table with previous date comparison

I am very new to DAX and I have been tasked with solving a problem, but I have now been staring at it for days now, and I have not come any closer to a solution. I hope you are able to help me.

To keep the problem as simple as possible, we can use the following two simple tables as input. Both tables are consisting of a date field, and a number field and one table has fewer records than the other.

Table A:

Date_A Number_A
1/12-2022 1
5/12-2023 1

Table B:

Date_B Number_B
1/12-2023 1
2/12-2023 1
4/12-2023 1
5/12-2023 1
6/12-2023 1

What I searching for is to have "Table A" be the main table, and then for each date in "A", show the sum of "Number_B", between the current date and the previous date in "Table A".

For example for Date_A = '5/12-2022', the previous date in Table A, is the 1/12-2022. Therefore I would like it to show the sum of Number_B from 1/12-2022, and including 5/12-2022, which is equal to 3.

I have written it in SQL, but do not know if that helps.

/*Data*/
WITH a AS (
    SELECT
        TO_DATE('01-12-2022') AS date_A,
        '1'                   AS number_A
    FROM
        dual
    UNION
    SELECT
        TO_DATE('05-12-2022') AS date_A,
        '1'                   AS number_A
    FROM
        dual
), 
b AS (
    SELECT
        TO_DATE('01-12-2022') AS date_B,
        '1'                   AS number_B
    FROM
        dual
    UNION
    SELECT
        TO_DATE('02-12-2022') AS date_B,
        '1'                   AS number_B
    FROM
        dual
    UNION
    SELECT
        TO_DATE('04-12-2022') AS date_B,
        '1'                   AS number_B
    FROM
        dual
    UNION
    SELECT
        TO_DATE('05-12-2022') AS date_B,
        '1'                   AS number_B
    FROM
        dual
    UNION
    SELECT
        TO_DATE('06-12-2022') AS date_B,
        '1'                   AS number_B
    FROM
        dual
)

/* SQL */ 
SELECT
    Date_tot,
    SUM(Number_A) as Number_A,
    SUM(number_B) AS Number_B
FROM
    (
        SELECT
            a.Number_A,
            b.Number_B AS number_B,
            COALESCE(a.date_A,
                MAX(a.date_A)
                OVER(
                ORDER BY
                    b.date_B
                ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING
                ))    AS Date_tot,
            b.Date_B
        FROM
            a,
            b
        WHERE
            b.date_B = a.date_A (+)
        ORDER BY
            b.date_B
    )
WHERE
    Date_tot IS NOT NULL
group by Date_tot
;

The resulting table should be the following.

Date_A Number_A Number_B
1/12-2022 1 1
5/12-2022 1 3

Also see that the following values in Table B, after the latest date in Table A should be ignored.

Upvotes: 0

Views: 504

Answers (1)

Muhammad Ali
Muhammad Ali

Reputation: 489

I hope, it helps.

if result is 3 use coalesce and < sign but if's 4 run the uncomment code

Sum_Number_B = 
VAR NUM_B_CAL = 
CALCULATE(
     COALESCE(SUM(Table_B[Number_B]), 1),
     FILTER(
         ALL(Table_B[Date_B]),
         Table_B[Date_B] < MAX(Table_B[Date_B])
     )
)
// CALCULATE(
//      SUM(Table_B[Number_B]),
//      FILTER(
//          ALL(Table_B[Date_B]),
//          Table_B[Date_B] <= MAX(Table_B[Date_B])
//      )
// )
RETURN NUM_B_CAL

enter image description here

Upvotes: 0

Related Questions