Hello Experts,
I was wondering how hard would be to implement the following in DAX.
I have a set of Indicators which are calculated externally every month. With the following particularity:
They are cumulative , ie, jan = jan ; feb = jan +x ; march = feb + y ; april = march + z, etc ;
however in the end of the Semester (1/2 year) the values re-start from 0:
ie jul = jul ; august = july + a ; september = aug + b, etc.
(not so relevant to the question but ) note that x, a, etc can be positive or negative
Im bringing these to power bi to run the following calcs;
Noting, there are 2 ways I need to present these Indicators values in a table:
Indictors type "result": Monthly Average
Indicator type "value": Actual Value
Here's how the raw data looks like
IND1A type Monthly
| Date | Customer | Grupo | Indicator | Value |
| jan-2018 | A | G1 | IND1A | 1 |
| feb-2018 | A | G1 | IND1A | 2 |
| march-2018 | A | G1 | IND1A | 4 |
| apr-2018 | A | G1 | IND1A | 5 |
mai | A | G1 | IND1A | 5 |
| jun | A | G1 | IND1A | 8 |
| jul | A | G1 | IND1A | 2 |
| aug | A | G1 | IND1A | 3 |
| sept | A | G1 | IND1A | 5 |
| oct | A | G1 | IND1A | 3 |
| nov | A | G1 | IND1A | 5 |
| dez | A | G1 | IND1A | 5 |
IND1B type Value
| Date | Customer | Grupo | Indicator | Value |
| jan-2018 | A | G1 | IND1B | 1 |
| feb-2018 | A | G1 | IND1B | 2 |
| march-2018 | A | G1 | IND1B | 4 |
| apr-2018 | A | G1 | IND1B | 5 |
mai | A | G1 | IND1B | 5 |
| jun | A | G1 | IND1B | 8 |
| jul | A | G1 | IND1B | 2 |
| aug | A | G1 | IND1B | 3 |
| sept | A | G1 | IND1B | 5 |
| oct | A | G1 | IND1B | 3 |
| nov | A | G1 | IND1B | 5 |
| dez | A | G1 | IND1B | 5 |
I have 12 indicators in 4 different groups, with diverse type of montly/value
now in powerbi I'd like to be able to show the results per quater and semester considering the 2 types of values above.
Ex:
Table sliced for Customer A ; say at November (e.g. only values till October are available)
| Group | | Q1 | Q2 | Q3 | Q4 |
| G1 | | | | | |
| | IND1A (monthly) | March value / 3 | Jun value / 6 | September value / 3 | October value / 4 |
| | IND1B (value) | March value | Jun Value | September value | October Value |
| G2 | | | | | |
| | IND2A (value) | March value | Jun Value | September value | October Value |
| | IND2B (monthly) | March value / 3 | Jun value / 6 | September value / 3 | October value / 4 |
Also would be great to present the semester view of the same as well, with:
Table sliced for Customer A ; say at November (e.g. only values till October are available)
| Group | | S1 | S2 |
| G1 | | | |
| | IND1A (monthly) | Jun value / 6 | October value / 4 |
| | IND1B (value) | Jun Value | October Value |
| G2 | | | |
| | IND2A (value) | Jun Value | October Value |
| | IND2B (monthly) | Jun value / 6 | October value / 4 |
So Id need a DAX to differentiate the indicator type before applying either a calculation based on the number of months available in the period or the last available value in the period; again considering at July the values restart from 0 ; ie data is presented accum till end of 1/2 year and then restart at July
Probably too much to ask ; not sure if it is ok to ask for help for a small fee on this forum 🙂
Thx in advance,
Vladimir
