Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
I have the below data, I am trying to get results as below, here I have 2 facts on Quarter and Month level, I need to show final results in table chart on a quarter level it should sum the fact 1 values on the quarter level if fact 1 does not exist it should take fact 2 for that month. here I am using the below expression.
Final Measure:
Sum(current) + if( Aggr(Sum(current), [Year-Month]) <= 0, Sum(future),0)
load * Inline [
Year-Qtr,Year-Month, current,future
2021-Q3, 2021-Jul, 30, 40
2021-Q3, 2021-Aug, 20, 50
2021-Q3, 2021-Sep, 10, 60
2021-Q4, 2021-Oct, 15, 20
2021-Q4, 2021-Nov, 25, 30
2021-Q4, 2021-Dec, 25, 50
2022-Q1, 2022-Jan, 10, 20
2022-Q1, 2022-Feb, 10, 20
2022-Q1, 2022-Mar, , 15
2022-Q2, 2022-Apr, , 20
2022-Q2, 2022-May, , 25
2022-Q2, 2022-Jun, , 30
2022-Q3, 2022-Jul, , 20
2022-Q3, 2022-Aug, , 50
2022-Q3, 2022-Sep, , 60
];
Here I should get 35 for 2022-Q1 since there is no fact1 value for 2022-Mar, so it should take the fact2 value as 15.
10+10+15=35
thanks for your support.