Problem with dax formula | Calculate an index with conditional over a period of 8 weeks

carddosogabriel — Sun, 17 Dec 2023 19:06:22 GMT

Good afternoon, everyone, can you help me?

I made a formula called "I8W" (Index of 8 weeks): which returns me:

0 if any SAP has had 0 volume for 8 weeks in a row
1 if any SAP has had a volume greater than 0 within 8 weeks

However, as you can see in the image below, it only returns 1 for the last date, I would like the 1 to be counted over the entire interval. I would also like to have the total sum filtered by each SAP and date.

Re: Problem with dax formula | Calculate an index with conditional over a period of 8 weeks

123abc — Mon, 18 Dec 2023 06:52:13 GMT

Certainly, I can help you with that! It sounds like you want to create an index that is 0 if there has been zero volume for 8 consecutive weeks and 1 if there has been any volume greater than 0 within an 8-week period. Additionally, you want the result to be aggregated over the entire interval for each SAP and date.

You can achieve this using DAX (Data Analysis Expressions) in Power BI. Here's a general approach:

Assuming you have a table named YourTable with columns like SAP, Date, and Volume, you can create a calculated column for your index using the following DAX formula:

I8W =
VAR CurrentDate = YourTable[Date]
VAR EightWeeksAgo = CurrentDate - 56 // 8 weeks * 7 days per week = 56 days

RETURN
IF (
CALCULATE(
SUM(YourTable[Volume]),
FILTER(
YourTable,
YourTable[Date] >= EightWeeksAgo &&
YourTable[Date] <= CurrentDate &&
YourTable[Volume] > 0
)
) > 0,
1,
0
)

This formula calculates the sum of the volume for the last 8 weeks, and if the sum is greater than 0, it returns 1; otherwise, it returns 0.

Remember to adjust column and table names according to your actual Power BI model.

To get the total sum filtered by each SAP and date, you can use this DAX measure:

TotalVolume = SUMX(FILTER(YourTable, YourTable[I8W] = 1), YourTable[Volume])

This measure calculates the sum of volumes for each SAP and date where the I8W index is 1.

Again, make sure to replace YourTable and column names with your actual table and column names.

These measures and calculated columns can be added to your Power BI report to achieve the desired results.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Problem with dax formula | Calculate an index with conditional over a period of 8 weeks

carddosogabriel — Fri, 22 Dec 2023 19:58:00 GMT

Unfortunately it didn't work, but I managed to solve it another way.

I ended up using DATESINPERIOD to do the calculation:

I8W = IF(
CALCULATE(
SUM('Table'[Net Sales Volume]),
DATESINPERIOD('Table'[Data], MAX('Table'[Data]),-56,DAY)) > 0
,1
,0
)

topic Re: Problem with dax formula | Calculate an index with conditional over a period of 8 weeks in DAX Commands and Tips

Problem with dax formula | Calculate an index with conditional over a period of 8 weeks

Re: Problem with dax formula | Calculate an index with conditional over a period of 8 weeks

Re: Problem with dax formula | Calculate an index with conditional over a period of 8 weeks