DAX calculation - v1

[Julieee299]

Hello Team,

I hope you & your family are safe and healthy.
I am struggling since couple of hours with some calculations I am trying to onboard under the model attached (pbix file).
The raw data is not in an optimal format (i tried to unpivot it but then I was getting even harder to create the formulas…)

Thus so far I am struggling with the table names measures in which:

Current AIC GBP should be = Calculate the Spend PY GBP + YTD GBP - Part Cap PY GBP only for the project where the status has been either Closed or submitted mode otherwise I want a blank result) --> I manage to it based on only 1 filter (calculation 1) but the calculation v2 is not working…

Ranking based on the YTD spend = I tried 2 differents formulas but dont lead to the result expected unfortunately ( I want to get a ranking on every project based on their YTD value and if the value is the same then the rank should be the same one ) like a Rank.EQ in excel …

The calculation name current delay should basically = // calculating …if a project status is “closed” or “commissioned” then “not delayed”, otherwise if Planned date <= current month (so here I assume March) then “delayed” otherwise “not delayed” …clearly I dont know where to start to tackle this one

and the last one will be based on this Delayed or not Delayed from the above doing a calculation to evaluate the number of month in between like: if Delayed then rounding(Current Month - Plan date / 30,25) + (LV Date / 1000000), 0) – I managed to do it in excel but impossible here

Many many thanks if advance you will saved me couple of hours / day for sure.
Kind regards,
Julie
Take care,
JT Test.pbix (332.9 KB)

[BrianJ]

@Julieee299,

First, thank you very much for the detailed explanation of the problem, and for posting a PBIX file. That’s an excellent start to getting the best response/support on the forum.

Before jumping into any DAX work, I always like to confirm two things:

1. is the data model set up correctly? – Yes. You’ve got a good, simple structure - date table, dimension table linked to fact table with one to many relationships. Check.
2. are the data tables set up appropriately for the analysis that needs to be done - unfortunately, as you suspected above the answer here is no. The “short and wide” structure (relatively few rows, lots of columns) is the exact opposite of what works best in most cases for DAX/Power BI. The individual month columns you have in your fact table are going to make it very difficult at best to get your DAX to work out properly.

Before we dive into fixing the DAX, we’re going to need to work on restructuring your fact table to reshape it into a “long and narrow” structure. To do that in Power Query, we’ll need your Excel data file. Here are a couple of recent threads discussing the short/wide versus long/narrow restructuring.

Revenue applied over time

Hello , I’d like to know if someone could help me out to figure a formula to spread a new work sold figure. Say I sold $7M in January, then I want to see how revenue flows in at a specific rate which is coming from this table below. For example if I sold 7 million in January , then the revenue expected for January is as in P7 (2% jan, 9% feb, 11% march … so on and so forth). is there an specific technique I could use to get this calculated in powerbi? below the matrix I want to use to calculat…

By- Month Subscriptio Revenue using only Subscription Start and End Date (or "N/A" as End Date)

Hi, I am trying to calculate the Subscription Revenue by Month but I only have Start Date, End Date. If the subscription is ongoing, the End Date = “N/A”. My goal is to show, December Subscription Revenue = $x, January Subscription Revenue = $y, etc. I am using the date table from Enterprise DNA’s class to get the Months and the attached file as my data. I’d appreciate any help you can offer. I tried using a filter table formula, but adding it here will just be more confusing. Subscription…

Confusion in using "Thin and Long Fact Table"

Dear All, I am really confused that how to do following tasks: 1- Not easily able to do tasks such as finding Standard Deviation etc. 2- Histograms and data rank categorization need calculated columns by row distinct value to assign grades to the students. [Final%20Result] For this purpose, I have to use the flat and wide table so that it can be done. [Flat%20Result%20Table] Should I use such duplicate table or not? I am really surprised that how it’s so difficult to do some simple task…

Also, please take a look at the following regarding your message to @Greg about response time on the forum:

https://forum.enterprisedna.co/t/asking-questions-on-the-enterprise-dna-support-forum/30

Thanks. I hope this is helpful. Once we have the base Excel data, either @Greg, I or someone else on the forum can help you work out the rest of this.

• Brian

[Julieee299]

Hello Brian and thanks very much for your reply - sorry for the rule of mentioning a member didnt see this before and will not do it .

Indeed I knew form different dashboard I did that the data here is not shaped in an optimal way and I tried different query transformation - unsuccessful to lead to a comprehensive model - I tried to unpivot the month but then I got duplicate for the different spends etc — Ideal would be to have only one project dimension mentioned and then monthly value for sure but I didnt manage to create this.

Please find attached the excel table related (fact table & mapping for currency translation)
Thanks very much again.
Take care

Julie

P3 Capex - FOR BI - small.xlsx (36.9 KB) Mapping FX.xlsx (18.2 KB)

[BrianJ]

@Julieee299,

Great – thanks very much for the quick response in providing the Excel file.

@Greg - are you already working this one? Please let me know either way - I don’t want to duplicate efforts

Thanks.

• Brian

[BrianJ]

@Julieee299,

Okay, I think I got all four measures working to your specifications. I did have a couple of questions, noted below.

1. AIC GPB

Current AIC GPB Filtered =

CALCULATE(
    [Spend PY GBP] + [YTD GBP] - [Part Cap PY GBP],
    FILTER(
        'Project data',
        'Project data'[Status] = "Closed" ||
        'Project data'[Status]= "Submitted"
    )
 )

2. Ranking

Question: I interpreted smaller numbers here as being good and getting a better rank. Is that correct? If not, just change the ASC parameter to DESC.

Rank Proj YTD = 

VAR RankProj =
RANKX(
    ALL( 'Project data'[Project name ] ),
    [YTD GBP],,
    ASC,
    Dense
)

RETURN
IF(
    HASONEVALUE( 'Project data'[Project name ] ),
    RankProj,
    BLANK()
)

3. Delay Status

   Delayed =

   VAR DelayStatus =
   SWITCH( TRUE(),
      SELECTEDVALUE( 'Project data'[Status] ) = "Closed", "Not Delayed",
      SELECTEDVALUE( 'Project data'[Status] ) = "Commissioned", "Not Delayed",
      SELECTEDVALUE( 'Project data'[Planned Date] ) >= TODAY(), "Not Delayed",
      "Delayed"
   )
   
   RETURN
   IF(
       HASONEVALUE( 'Project data'[Project name ] ),
       DelayStatus,
       BLANK()
   )
   

4. Months of Delay, rounded to nearest quarter month

Question: I couldn’t figure out in your initial post what the purpose of LV Date / 1000000 was, so I didn’t include it in the calculation below. If that’s incorrect, please let me know (seemed like maybe it was some sort of Excel kludge?)

Months Delayed = 

VAR Diff =
IF(
    [Delayed] = "Delayed",
    DATEDIFF(
        TODAY(),
        SELECTEDVALUE( 'Project data'[Planned Date] ),
        DAY
    ),
    0
)

RETURN
MROUND(
    DIVIDE(
        Diff,
        30,
        0
    ),
    -0.25
)

Please let me know if you have any questions about any of the measures.

Also, while I didn’t change your data model, it is a bit of a ticking bomb temporarily defused by your clever SWITCH statement. However, if someone forgets to add the additional statement to the SWITCH each month, or more critically, when you cross 12 months, and the month names begin to repeat, it will start throwing incorrect values for any time-based measure related to GPB. Would be glad to chat about alternative data model structures if you’d like.

I hope this is helpful. Full solution file attached below.

• Brian
  eDNA Forum - JT Test Solution.pbix (340.5 KB)

[Julieee299]

Hello Brian,

Thanks very much for your reply and your kindness to help me
I went to your model and I have adapted mine (will attached it as well the new one) + the excel table where I have replicated the formulas I need to achieve for the 1)delayed and 2)month delays 3)month bucket

Its a bit tricky cause the date against which I need to evaluate this “delayed” is based on the date of the extract of this report (1 months lag always so here the data is March 2020) - reason why I created the current month measure (current month -1 so giving me March - then in May will be April etc logic is fine).

Even though if I switch to current month the year is not taken into consideration thus the result is not accurate (see page 2 of the pbix).

Very smart the icon logic next to the delay but I don’t understand why the icon logic is not working for me ^^ (you will see in page 2). For this kind of creation do you know which video I could go in to deep dive (like for example in the measure assessing in Budget or Overspend will be cool to have a arrow point high or equal sign next to the measure) - I could develop it myself if I found the right video from Enterprise
Same for the month bucket depending of the category would like to have different scaling color.

I attached the new version I reworked and the excel file where you see the formulas - page 2 mostly.
I will be super happy to discuss about my query itself as you see its not that easy to transform in a format where my DAX will never struggle haha.

Let me know what will be the best. In the meantime thanks again very much.
Kindest regards,
Julie
P3 Capex - FOR BI - small.xlsx (41.8 KB) JT Test.pbix (349.5 KB)

[BrianJ]

@Julieee299,

The reason your revised measures aren’t working is because you need to revise the logic in your time intelligence calculation/comparisons:

1. for example, Current Month can’t simply subtract one from the month number, since it will bomb in January. You will need to take account of that in a way such as this:

   Current Month =

   VAR Mo = MONTH( TODAY() )

   RETURN
   IF( Mo = 1, 12, Mo - 1 )

2. similarly, when you’re comparing month and year, you can’t concatenate month number and year number, since the logic doesn’t work:

122020 will evaluate as greater than 32021, when in fact the latter is the later date. Instead you need to concatenate year and month, with single digit months padded with a zero:

Current Month & Year = 

VAR PaddedMonth =
IF( LEN( [Current Month] ) = 1,
    CONCATENATE( "0", [Current Month] ),
    [Current Month]
)

VAR TodayYear =
YEAR( TODAY() )

VAR CurrentYear =
IF( [Current Month] = 1,
    TodayYear - 1,
    TodayYear
)

RETURN
VALUE(
    CONCATENATE( CurrentYear, PaddedMonth )
)

Now 202103 will properly evaluate as being a later date than 202012.

I’ve gone through and fixed your measures, and put the revised measures in a separate Revised Measures table.

For your Month Bucket measure, I left that one for you to work out, but recommend you do so using SWITCH( TRUE() ) structure rather than a bunch of IF() statements.

Using SWITCH True Logic In Power BI - DAX Concepts

In order to revise the measures, I used a bunch of time intelligence functions including DATEADD, DATESBETWEEN and DATEDIFF. I recommend reviewing the changes I made after watching the following videos:

DATESBETWEEN, DATESINPERIOD, DATEDIFF functions

The best time comparison function - DATEADD

Finally, here are a couple of videos on conditional formatting techniques, like the icon measure I added to your report:

Conditional Formatting For Chart Visuals In Power BI - What's Possible?

Custom Conditional Formatting For Power BI Tables

Once you got all this down, and working as you’d like, give a shout and we can talk about a better data model structure - I’ve got some ideas on how to restructure that will make things easier for you in the long run.

Good luck! I hope this has been helpful. Revised solution file posted below.

• Brian

JT Test v2 solution2.pbix (369.8 KB)

[Julieee299]

Hello Brian,

I hope you are doing well.
Sorry for the late reply the day has been intense I have continued working on it.
I changed indeed to Switch true function for the month bucket looking better indeed.

Something weird in the page 2 is the “Current month & year” where the card visual is not in the same form as the current month (i tried to change the type by whole number but still very odd result).

Also I wonder why I dont get the total at the bottom of the my tab in page 2 - Would like to see for example the number of project delayed (so counting all the 1) etc etc.

I have reattached the updated one

Please let me know if we can discuss about the model itself as ideally would like to come back with something more easy to handle from Dax point of view if you prefer to have a call or I dont know but I am very keen in learning from you.

I watched the video you recommended indeed conditional formatting is quite cool and pretty straightforward to get - even though if I want to highlight based on the cluster (where its a text) is not that simple like for example all IFCN projects in Light green / Health in light blue and Wellness in pink…

Many thanks again for the support.
KR
Julie

JT Test.pbix (361.4 KB)

[Julieee299]

Hello Brian,

I hope you are doing well ?
I went to the model & online course on helping to fix complex total errors (as in my file I would like to have the number of delayed project being sum under the row total)
I tried different one but not getting the good results like:

• Number of delayed project = IF([Current Delay] = “Delayed”, COUNT(‘Project data’[Current Delayed]),"") --> this one works but not on the total where I get zero :s

so I tied this

-Number of delayed project v1= SUMX(SUMMARIZE(‘Measures - Delays & Ranking’,“Current Delay”,“Delayed”),COUNTX(‘Measures - Delays & Ranking’,[Current Delay]=“Delayed”))

or

-Number of delayed project v2 =COUNTX(SUMMARIZE(‘Project data’,‘Project data’[Project name ],“delayed”,‘Measures - Delays & Ranking’[Current Delay]),COUNT(‘Project data’[Project name ]))

not getting the good result either.

Any idea how to proceed has the first need to evaluate and count of the project is delayed but then need to get to sum of all these individuals count…

Let me know if you want to discuss and chat about the query if you have time or later in the week.
Many thanks
KR
Julie

[BrianJ]

@Julieee299,

There are many different ways to handle this type of problem with totals. Here’s one way that I find straightforward to understand and easy to debug:

1. Using variables, create a calculation for the total. In this case, I’ve used COUNTROWS to count the number of rows in the project table after that table has been filtered down to the delayed projects.

2. Use the IF (HASONEVALUE() ) construct to determine if you are within a single project row within the visual ( HASONEVALUE = True ) or within the total row ( HASONEVALUE = False ), and return the correct variable in each case.

Here’s what it looks like applied to your visual, now showing the correct total:

• Brian

[Julieee299]

Hello Brian,

Thanks a lot for coming back. I hope you are doing well and your family is safe & healthy.
I spent quite some time also during the week and finally manage to get a solution as well.
Basically I used the Summarize function that Sam explained over some modules :).
Also I am not working on getting for each month bucket of delayed so “between 0 to 2 months”, “Between 3 to 6 month”, “between 7 to 9 months”, “between 10 to 12 Months” and “Over 1 year” the number of delayed projects - and I am struggling a lot:

I tried:
Count Month Bucket = COUNTROWS(FILTER(‘Measures - Delays & Ranking’,[Month bucket]= “Between 0 to 2 Months”)) but I need every single month bucket but even the formula doesnt provide me the right result…

I would like to show a graph like this one but where instead of Factory I will see every bucket and I will have the number of delayed project lets see per bucket and per Factory…

any thoughts ?
Again if you are still willing to have a chat about the model itself I am super keen in learning from you.
Many thanks for all the support.
Kind regards,
Julie

[EnterpriseDNA]

Hi @Julieee299, It’s great to know that you are making progress with your query. Please be reminded that asking more than one question in a forum thread and asking question after question in the same forum thread around the same project or piece of development work is considered inappropriate. For further questions related to this post, please make a new thread. More details can be found here - Asking Questions On The Enterprise DNA Support Forum