cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## Use measures for line chart to show trend.

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.

thank you,

1 ACCEPTED SOLUTION
Helper I

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

8 REPLIES 8
Super User

@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!

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper I

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

Super User

@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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper I

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))

))

Super User

@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

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] ) ``````

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Helper I

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.

Super User

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.

If I took the time to answer your question and I came up with a solution, please mark my post as a solution and /or give kudos freely for the effort 🙂 Thank you!

Proud to be a Super User!

Helper I

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

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors