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 have a data source with data in rows and I want to put them in colum to do formulas between the columns.
So it looks something like this:
product | year | sales |
a | 2018 | 25 |
a | 2019 | 264 |
a | 2020 | 651 |
b | 2018 | 810 |
b | 2019 | 245 |
b | 2020 | 369 |
So what I do is I pivot the column in power query like this:
Product | 2018 | 2019 | 2020 |
a | 25 | 264 | 651 |
b | 810 | 245 | 369 |
Grand Total | 835 | 509 | 1020 |
Then I do formulas like variance accross the column by creating measure, etc.
Like:
variance = (sum(2019) - sum(2018) )
variance% = iferror( [variance] / sum(2018) , "")
My problem is that next year the column will have changed from 2018, 2019 and 2020 to 2019, 2020 and 2021. Is there a way to reference to these dynamic pivoted column so I don't have to redo the formulas (measure) every year?
Solved! Go to Solution.
Hi @Anonymous ,
If I understand you correctly, I think you don't need to change your data structure.
Please try something like so:
1. Create Measures:
variance =
VAR ThisYear =
MAX ( 'Table'[year] )
VAR LastYear = ThisYear - 1
VAR ThisYearSum =
SUM ( 'Table'[sales] )
VAR LastYearSum =
CALCULATE ( SUM ( 'Table'[sales] ), 'Table'[year] = LastYear )
RETURN
ThisYearSum - LastYearSum
variance% =
VAR ThisYear =
MAX ( 'Table'[year] )
VAR LastYear = ThisYear - 1
VAR LastYearSum =
CALCULATE ( SUM ( 'Table'[sales] ), 'Table'[year] = LastYear )
RETURN
IFERROR ( [variance] / LastYearSum, BLANK () )
2. Put the Measures above in a table visual or a Matrix visual.
For more details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
If I understand you correctly, I think you don't need to change your data structure.
Please try something like so:
1. Create Measures:
variance =
VAR ThisYear =
MAX ( 'Table'[year] )
VAR LastYear = ThisYear - 1
VAR ThisYearSum =
SUM ( 'Table'[sales] )
VAR LastYearSum =
CALCULATE ( SUM ( 'Table'[sales] ), 'Table'[year] = LastYear )
RETURN
ThisYearSum - LastYearSum
variance% =
VAR ThisYear =
MAX ( 'Table'[year] )
VAR LastYear = ThisYear - 1
VAR LastYearSum =
CALCULATE ( SUM ( 'Table'[sales] ), 'Table'[year] = LastYear )
RETURN
IFERROR ( [variance] / LastYearSum, BLANK () )
2. Put the Measures above in a table visual or a Matrix visual.
For more details, please check the attached PBIX file.
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous ,
Try this:
variance =
VAR ThisYear =
MAX ( 'Table'[year] )
VAR LastYear = ThisYear - 1
VAR ThisYearSum =
SUM ( 'Table'[sales] )
VAR LastYearSum =
CALCULATE ( SUM ( 'Table'[sales] ), 'Table'[year] = LastYear )
RETURN
IF (
ThisYear = MINX ( ALLSELECTED ( 'Table' ), 'Table'[year] ),
BLANK (),
ThisYearSum - LastYearSum
)
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
It works, thank you!
I'm sure all the solutions work but @Icey solution is the easiest to implement!
You can create datesytd or totalytd or trailing measure with Date calendar
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete 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"))
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
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Not in DAX that I am aware of. However, easily done in Power Query. Then you just reference these fixed column names in DAX.
Paste this blob of M code in a blank new query in Power Query using the Advanced Editor.
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WSlTSUTIyMLQAUaZKsTpwEUsQZWaCJGRkAKTMTA3BQkkIfRaGBshCYI0mpkhCYI3GZpZKsbEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [product = _t, year = _t, sales = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"product", type text}, {"year", Int64.Type}, {"sales", Int64.Type}}),
#"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Changed Type", {{"year", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Changed Type", {{"year", type text}}, "en-US")[year]), "year", "sales", List.Sum),
#"Renamed Columns" = Table.RenameColumns(#"Pivoted Column",{
{Table.ColumnNames(#"Pivoted Column"){1}, "2 Yr Ago"},
{Table.ColumnNames(#"Pivoted Column"){2}, "1 Yr Ago"},
{Table.ColumnNames(#"Pivoted Column"){3}, "Current Year"}
})
in
#"Renamed Columns"
The function Table.Columns(#"Pivoted Column"){n} is getting the name of the Nth column (Power Query indexes at 0) and renaming it to whatever you want. It looks like this:
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingCovering 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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |