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.
Hi Experts,
I have a table as below in my Power BI:
2022 2023 2024 Next_Order_Year
0 1 2 2025
0 1 0 2024
2 0 1 2023
I want a to write a DAX that will return a column "Total_Prior_Next_Order_Year" that will count all the values before the year in the Next_Order_Year.
Example, For the first row, it will add all the values as the year 2022, 2023 and 2024 are prior to 2025 and the result will be 3.
For the second row, it will add all the values for the column 2022 and 2023 as these are prior to 2024 and the result will be 1.
For the third row, it will take only the value under column 2022 as it is the only year prior to 2023 and the result will be 2.
Thank you.
here is a workaround for you.
1. create a reference table , user hearder as first row , transpose table and remove text value.
2. create a column in original table
Column = CALCULATE(COUNTROWS('Table (2)'),FILTER('Table (2)','Table (2)'[Column1]<'Table'[nextorderyear]))
pls see the attachment below
Proud to be a Super User!
Hi, I think you should consider unpivoting your data.
Step 1. Unpivot your data
Simply select Next_Order_Year (and other columns if you have) and from Ribbon > Transform > Unpivot Columns > Unpivot Other Columns
= Table.UnpivotOtherColumns(Source, {"Next_Order_Year"}, "Year", "Value")
Step 2. Add condition for Next_Order_Year
= Table.AddColumn(#"Unpivoted Other Columns", "IsYearOK", each [Year] < [Next_Order_Year])
Step 3. Keep only rows with IsYearOK = True or you can leave it here (if you need that data) and filter it with a measure or on the visual
Power Query M:
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlDSUTIEYiMQNjAyVYrVQQgaQARNwIJGUAFDiKCxUmwsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"2022" = _t, #"2023" = _t, #"2024" = _t, Next_Order_Year = _t]),
#"Unpivoted Other Columns" = Table.UnpivotOtherColumns(Source, {"Next_Order_Year"}, "Year", "Value"),
#"Added Custom" = Table.AddColumn(#"Unpivoted Other Columns", "IsYearOK", each [Year] < [Next_Order_Year]),
#"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Next_Order_Year", Int64.Type}, {"Year", Int64.Type}, {"Value", Int64.Type}, {"IsYearOK", type logical}})
in
#"Changed Type"
Proud to be a Super User!
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 |
---|---|
115 | |
99 | |
88 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |