Use measures for line chart to show trend.

[AVGUser_PBI]

Hello Everyone!

Context:

I  want to show a trend line for project cost spread over current year. Each month project cost will be, ProjCost = (Recorded cost so far + estimated cost for remainder of the year) I have two fact tables, 1.recorded cost 2. estimated cost. 

each month real cost comes in for past month and new estimated cost is uploaded for remainder of the year. recordedcost has effective date and costs are associated with effective dates. whereas, estimatedcost has effective date as well as capture date such that every capture date will have cost data that is associated with effective date for future month.

I've created us effectivedate and capturedate as date dimenstion tables:

I created project cost measure for each month as below:

FebCost = CALCULATE(
                        CALCULATE(SUM('recordedcost'[amount], FILTER('effectivedate', 'effectivedate'[date].[date] < DATE(2023,02,01))) 

                          +

                         CALCULATE(SUM('estimatedcost'[amount], FILTER('capturedate', 'capturedate'[date].[date] = DATE(2023,02,01))

                             ))

...

NovCost = CALCULATE(
                        CALCULATE(SUM('recordedcost'[amount], FILTER('effectivedate', 'effectivedate'[date].[date] < DATE(2023,11,01))) 

                          +

                         CALCULATE(SUM('estimatedcost'[amount], FILTER('capturedate', 'capturedate'[date].[date] = DATE(2023,11,01))

                             ))

I created 12 measures for each month as above. Now I want to create a line chart where X axis is a month (or measure name) and Y axis is value of measure, thus showing a line chart for a trend.

Please share your ideas or any suggestions to achieve this. 

thank you,

[parry2k]

@AVGUser_PBI this is not going to work, you have to use what I provided and if that is not working then share what you did otherwise I don't know how I or anyone else can assist. Thank you!

[AVGUser_PBI · Accepted solution]

I read on internet that it is not yet possible to have a measure on Xaxis. I created data views to accomodate my requirements. 

[parry2k]

@AVGUser_PBI can you share the dax measure you have created? I'm worried some things get lost in assumptions, better to see what you are doing.

[AVGUser_PBI]

Measure I created using dax are showing the correct data. calculations are correct. My problem is, i am unable to get measure on Xaxis and their values on Yaxis for a line chart as shown on question. 

measures:

FebCost = CALCULATE(
                        CALCULATE(SUM('recordedcost'[amount], FILTER('effectivedate', 'effectivedate'[date].[date] < DATE(2023,02,01))) 

                          +

                         CALCULATE(SUM('estimatedcost'[amount], FILTER('capturedate', 'capturedate'[date].[date] = DATE(2023,02,01))

                             ))

...

NovCost = CALCULATE(
                        CALCULATE(SUM('recordedcost'[amount], FILTER('effectivedate', 'effectivedate'[date].[date] < DATE(2023,11,01))) 

                          +

                         CALCULATE(SUM('estimatedcost'[amount], FILTER('capturedate', 'capturedate'[date].[date] = DATE(2023,11,01))

                             ))

[parry2k]

@AVGUser_PBI oh boy! You are on a very sloppy track but we are here to help 🙂 

As a best practice, add a date dimension in your model and use it for time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools. Check the related videos on my YT channel

Add Date Dimension
Importance of Date Dimension
Mark date dimension as a date table - why and how?
Time Intelligence Playlist

Once you have the date dimension, create relationships with your tables (both tables) on the date column from this new date table. you need to add one measure, and then use a column from the date table on the x-axis and measure on the y-axis

Measure = 
CALCULATE (
   SUM ( RecordCost[Amount] ),
   FILTER ( ALLSELECTED ( DateTable[Date] ), DateTable[Date] <= MAX ( DateTable[Date] ) )
) +
SUM ( EstimatedCost[Amount] ) 

[AVGUser_PBI]

Hi Thank you for the reply. I forgot to mentoin that I've created date dimensions using calendarauto() and effectivedate and capturedate in above dax are date tables. 

I tried your formula, is it for running sum? it doesn't give me the right calculations. date on X axis and measure on Y axis gives me a line chart for one measure. what I want is measures on x axis and its value on Y. 

[vanessafvg]

please share the structure of your data as its impossible to help without that information.

Its also best to provide sample data in text format.

[AVGUser_PBI]

Hi, i have edited my post. Please have a look at it.