Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
My problem is a bit difficult to explain in words alone, so here's a spreadsheet to make it clearer:
I have 2 fact tables. Columns with black text are columns that I have, and columns in blue are ones that I don't have.
The only number I care about obtaining is the one on the far right (in blue). My dataset is small so creating calculated columns isn't a problem if necessary.
I have created a rolling 3 month sum measure as follows:
Rolling 3 month sum =
CALCULATE(
SUM(Table1[COGS]),
DATESINPERIOD(Table1[Date],
LASTDATE(Table1[Date]), -3, MONTH)
)
but when dividing 'Table2'[Balance] by the result in this measure, it needs to divide it by country, not as a whole.
Any help is appreciated as always 🙂
Solved! Go to Solution.
Hi @DCELL ,
I created a model with the following tables:
The dimension table are use to make a relationship between the COGS and Balance table then create the following two measures:
Rolling 3 month sum =
IF (
DATEDIFF (
MINX (
DATESINPERIOD ( 'Calendar'[Date]; LASTDATE ( 'Calendar'[Date] ); -3; MONTH );
'Calendar'[Date]
);
MAXX (
DATESINPERIOD ( 'Calendar'[Date]; LASTDATE ( 'Calendar'[Date] ); -3; MONTH );
'Calendar'[Date]
);
DAY
) < 61;
BLANK ();
CALCULATE (
SUM ( COGS[COGS] );
DATESINPERIOD ( 'Calendar'[Date]; LASTDATE ( 'Calendar'[Date] ); -3; MONTH )
)
)
Balance 3 Months =
IF (
[Rolling 3 month sum] = BLANK ();
BLANK ();
SUM ( Balance[Balance] ) / [Rolling 3 month sum] * 91
)
See result below and in attach file:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @DCELL ,
I created a model with the following tables:
The dimension table are use to make a relationship between the COGS and Balance table then create the following two measures:
Rolling 3 month sum =
IF (
DATEDIFF (
MINX (
DATESINPERIOD ( 'Calendar'[Date]; LASTDATE ( 'Calendar'[Date] ); -3; MONTH );
'Calendar'[Date]
);
MAXX (
DATESINPERIOD ( 'Calendar'[Date]; LASTDATE ( 'Calendar'[Date] ); -3; MONTH );
'Calendar'[Date]
);
DAY
) < 61;
BLANK ();
CALCULATE (
SUM ( COGS[COGS] );
DATESINPERIOD ( 'Calendar'[Date]; LASTDATE ( 'Calendar'[Date] ); -3; MONTH )
)
)
Balance 3 Months =
IF (
[Rolling 3 month sum] = BLANK ();
BLANK ();
SUM ( Balance[Balance] ) / [Rolling 3 month sum] * 91
)
See result below and in attach file:
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
135 | |
118 | |
101 | |
71 | |
61 |