Forum Discussion
Problem with metric calculation
Hello everyone, my level on DAX language is beginner and I’m facing a project where other stakeholders know less about it than I do.
I have this kind of table
From the Fact table, I have to calculate a metric as the sum of the Sales (identified by the data type = "FP-Sales") from the week in which there is the first data INTAKE (data type= INTAKE) until the next 8 weeks, so for example if I find for that article X of the intake value on week 22 I have to add up the FP sales from week 22 to that of 30.
I calculated the week in which the intake value appears as:
Min week Intake = MINX( FILTER('Fact'; 'Fact'[Data type]="INTAKE");'Fact'[Week])
and I calculated also the 8 weeks (I know I can do it without creating a measure) but I was not sure of the correct output so:
End Discovery Sellout = =[Min Week Intake] + 8
So to calculate the sum of sales in this period I created this metric:
Discovery Sellout =SUMX('Fact'; IF('Fact'[Data type] = "FP-Sales" &[Min Week Intake]<='Fact'[Week] &&'Fact'[Week]=[End Discovery Sellout];'Fact'[QTY])
But it is not the desired result, because it is as if it calculated the metric on the lines where the data type = "INTAKE" is presented
I don’t understand if I’m doing something wrong (definitely! ) but I don’t know where to hit my head 😞
2 Replies
Trave97 Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.Hi Greg_Deckler
forgive me if I specified it wrong, the problem is that from this type of data (I attach sample file and screen).I would like to create a metric that calculates for each product the total QTY for the type of data FP-Sales, not taking all records with this condition but only those that fall within the range (week where the first intake value is presented and the next 8 weeks) in this example then I would like to have a metric that for the article A1CC2PA140C102 I have a value equal to (3+2+1=6) because the week corresponding to these values meet the condition, that is ( the week in which the first value = 17 and the next 8=25 [INTAKE DATA TYPE])
Week Data type QTY Product 17 Intake 1 A1CC2PA140C102 16 FP-Sales 1 A1CC2PA140C102 15 FP-Sales 7 A1CC2PA140C102 22 FP-Sales 3 A1CC2PA140C102 27 FP-Sales 2 A1CC2PA140C102 27 FP-Sales 1 A1CC2PA140C102 29 FP-Sales 6 A1CC2PA140C102 29 FP-Sales 5
A1CC2PA140C102 Let me know if I was clear that way,
thanks
