Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
Vladracs
Helper I
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

 

DateCustomerGrupoIndicatorValue
jan-2018AG1IND1A1
feb-2018AG1IND1A2
march-2018AG1IND1A4
apr-2018AG1IND1A5

mai

AG1IND1A5
junAG1IND1A8
julAG1IND1A2
augAG1IND1A3
septAG1IND1A 5
octAG1IND1A3
novAG1IND1A 5
dezAG1IND1A 5

IND1B type Value

DateCustomerGrupoIndicatorValue
jan-2018AG1IND1B1
feb-2018AG1IND1B2
march-2018AG1IND1B4
apr-2018AG1IND1B5

mai

AG1IND1B5
junAG1IND1B8
julAG1IND1B2
augAG1IND1B3
septAG1IND1B 5
octAG1IND1B3
novAG1IND1B 5
dezAG1IND1B 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 Q1Q2Q3Q4
G1     
 IND1A (monthly)March value / 3Jun value / 6September value / 3October value / 4
 IND1B (value)March valueJun ValueSeptember valueOctober Value
G2     
 IND2A (value)March valueJun ValueSeptember valueOctober Value
 IND2B (monthly)March value / 3Jun value / 6September value / 3October 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 S1S2
G1   
 IND1A (monthly)Jun value / 6October value / 4
 IND1B (value)Jun ValueOctober Value
G2   
 IND2A (value)Jun ValueOctober Value
 IND2B (monthly)Jun value / 6October 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









2 REPLIES 2
hnguy71
Memorable Member
Memorable Member

Hi @Vladracs,

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?



Did I answer your question?
Please help by clicking the thumbs up button and mark my post as a solution!

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 😕

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.