cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Helper I

## Looking for an expert to help me with a complex DAX?

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 🙂

2 REPLIES 2
Memorable Member

This doesn't seem too complicated but would you be able to supply some expected outputs? Possibly in a table format and we can maybe assist further?

Helper I

Thanks a lot for replying.... I'll add an actual table an pbi and post back here , with some further complications I received from the analyst 😕