Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Good afternoon, everyone, can you help me?
I made a formula called "I8W" (Index of 8 weeks): which returns me:
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.
.
Solved! Go to Solution.
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.
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
)
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.
User | Count |
---|---|
50 | |
23 | |
18 | |
18 | |
14 |
User | Count |
---|---|
91 | |
85 | |
46 | |
28 | |
21 |