Using measure as calculation on x-axis

[CarelJordaan]

I need to show uptake % against [Days From Go-Live], which is a differance between two dates calculation but is given me issues to display on a line diagram.

From other forum posts I have seen that a measure cannot be used as the X-axis value but I am struggling to think of possible solutions.

I have created this PBIX which has order datasets for intake, cancellations, terminations and installations and related to a Date table based on the milestone date. The orders are also related to a Aggregation Zone using the [AG Code].

There is a lot of measure related to the cumalative values of the various milestone and 2x date measures:

- [M-First Install Date] which is the date value of the first order installed for a Aggegation Zone and is a fixed value captured in the Aggregation Zone table.
- [Days From Go-Live] which is the difference between the Date value in the date table and [M-First Install Date]

Days From Go-Live = If([Calculate Value] = "Yes" , VALUE(MAX('Days_Power BI Data Report'[Date]) - [M-First Install Date]) , BLANK())

*[Calculate Value] measure is used to only calculate when the first order was placed.

Below is examples of 3 Aggregation Zones but there is data for 100's of them that need to be displayed/filtered.

Both of these measures work fine and calculate dynamically based on the filters applied and per AG Code as shown below

The purpose of [Days From Go-Live] is to be able to show the same relative value for different Aggregation Zones with negative values being prior to first install and positive being after first install regardless of the date this happened on.

Below is how the visual looks with the Date value set as the X-axis and the measure added as a tooltip to show what the value is for each Aggregation Zone. As can be seen below the [Days From Go-Live] value for each AG Code is different on 2023/02/10

This is not ideal as the chart should look as shown below with the [Days From Go-Live] being the X-Axis values. Here is an Excel file with example values of what is required.

I am quite new to Power BI and most of my knowledge comes from Youtube videos and Power BI Forum posts. 

Is there a way for me to display the visual as required with the PBIX as is or is there another way to calculate and display this [Days From Go-Live] for each Aggregation Zone?

[CarelJordaan]

I managed to solve the problem by intorducing a new table that has a list of dates generated for each AG Zone and thus the existing measures could be used to create a calculated column for which the existing relationship work and would update to calculate the correct values for that AG Zone.
This video on generating date lists assisted me with the input from @Idrissshatila .

[CarelJordaan]

Hi @Idrissshatila 

Could you please eloborate on the solution?

In which table should this calculated column be created and how what should the DAX expresion look?

I have tried the 2 expressions below but they do not work as one gives a fixed value and the other is not dynamic as it just calculates the difference to the minimum value.

CC-Days From Go-Live = VALUE(MAX('Days_Power BI Data Report'[Date]) - [M-First Install Date])

CC2-Days From Go-Live = DATEDIFF(MIN('Aggregation Zone_Power BI Data Report'[First Installed Order Date Only]),'Days_Power BI Data Report'[Date],DAY)

[Idrissshatila · Accepted solution]

Hello @CarelJordaan ,

So in order to work as x-axis, you need to create the same measure but as a calculated column, thus you'll have numbers to view as days in x-axis.

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote For my Idea 💡