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.
Hello! I'm really new to Power BI so still learning the ropes.
I have different calculated measures:
I use this calculated measures in my line graphs. My ultimate goal is to have a dynamic column name (for the years) and since it's not possible, I searched for other ways. My goal is to make a seperate table with this with the ff values:
Types | Values |
2019 Type A | Past Year - Type A value |
2019 Type B | Past Year - Type B value |
2019 Type C | Past Year - Type C value |
2020 Type A | Current Year - Type A value |
2020 Type B | Current Year - Type B value |
2020 Type C | Current Year - Type C value |
Edit: The above table will be used in a line chart. I just want it to have a dynamic title so user will no longer maintain it much. This is related to my other post.
Here is my sample data where my calculated measures are based from:
Order Date | Values | Type |
04/08/2019 | 435 | A |
05/07/2019 | 68 | A |
11/09/2019 | 2 | B |
04/09/2019 | 45 | C |
10/23/2019 | 12 | C |
01/01/2020 | 3 | A |
05/18/2020 | 23 | B |
06/17/2020 | 12 | B |
06/17/2020 | 2 | C |
08/19/2020 | 12 | C |
07/03/2020 | 34 | C |
Advance thanks!
Solved! Go to Solution.
@crln-blue , See attached if that line visual works
Hi @crln-blue ,
According to my understand, you want to calculate the difference between Current Month and Last Month dynamically , right?
You could use the following steps or take a look at my pbix file here.
1.Create a YearMonth Column
YearMonth =
YEAR ( 'Table1'[Order Date] ) * 100
+ MONTH ( 'Table1'[Order Date] )
2. Create a table for Date Slicer
DateSlicer =
ALLSELECTED ( Table1[YearMonth] )
3.Calculate the difference:
difference =
VAR _sele =
SELECTEDVALUE ( DateSlicer[YearMonth] )
VAR _diff =
_sele - MAX ( 'Table1'[YearMonth] )
VAR _sum =
IF ( _diff = 0 || _diff = 1 || _diff = 89, SUM ( 'Table1'[Values] ), BLANK () )
VAR currMinusPre =
CALCULATE (
SUM ( 'Table1'[Values] ),
FILTER ( Table1, 'Table1'[YearMonth] = _sele )
)
- CALCULATE (
SUM ( 'Table1'[Values] ),
FILTER (
Table1,
'Table1'[YearMonth] = _sele - 1
|| 'Table1'[YearMonth] = _sele - 89
)
)
RETURN
IF ( HASONEVALUE ( 'Table1'[YearMonth] ), _sum, currMinusPre )
My visualizations look like this:
Did I answer your question ? Please mark my reply as solution. Thank you very much.
If not, please upload some insensitive data samples and expected output.
Best Regards,
Eyelyn Qin
@crln-blue ,Output format is not clear. See if the attached file after signature can help
You can connect to a date table and use time intelligence for this year vs last year.
Example
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
//Only year vs Year, not a level below
This Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('order'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
Appreciate your Kudos.
Hello @amitchandak , thank you for the help and to the links! I can compute the LYTD and YTD but what I'm aiming for is to be used it to a line chart, not in a matrix chart. I apologize for the confusion on the output, I forgot to put it on my post.
@crln-blue , See attached if that line visual works
Hello @amitchandak , thanks again for your answer. I'll replicate it on my end and will get back to you for further questions. Thanks!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
101 | |
69 | |
69 | |
43 |
User | Count |
---|---|
146 | |
106 | |
105 | |
90 | |
65 |