Forum Discussion

KristyP's avatar
KristyP
Helper I
2 years ago

Usage forecast DAX

Hi,   I am trying to develop a matrix in Power BI to forecast usage based on previous data.   We would like the data to be presented as per below:     Each serial number has it's own lin...
Need Help
v-weiyan1-msft's avatar
v-weiyan1-msft
Community Support
2 years ago

Hi KristyP , 

 

From your description, please try the following steps:

My Sample:

1. Please do pivot and other operations in Power query as described in the following advanced code.

 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdPNbsMgDAfwV5lyrpBtMB/HNtkb7Fb12OO0aR+HvX1t2i6mLCxCgqDwk/lDjsdp/v78ens9fzztp9308vN+roM9kpcOQR7pCYgcSis6Geg+6R2iw+m023SIWD/lnvEwUg6N4mO7YlX4QQkbjA4OAHhb0jtoIZC2Ac0ymEtRKP6xqzRmZsssXENOphxgp3kVXhUo3a5+laUqrEsw211JErm+wBBamniopsxsIXDpOserk6XGAZM41pQfDHve/xGxaPW5LyTbYLzzOFI45CZLiTc6rQ3tYUNy41RIzzrYS3Njwph5bq4wAOkSGy7ItnQut3fYw8jhfP8VVsdXx1PrEI+ca8TehhMchf6XUuZ0AQ==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Customer = _t, Type = _t, #"Serial Number" = _t, #"2022 Usage" = _t, #"2022 Read date" = _t, #"2023 Usage" = _t, #"2023 Read Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Type", type text}, {"Serial Number", type text}, {"2022 Usage", Int64.Type}, {"2022 Read date", type date}, {"2023 Usage", Int64.Type}, {"2023 Read Date", type date}}),
    #"Sorted Rows" = Table.Sort(#"Changed Type",{{"2022 Read date", Order.Ascending}}),
    #"Pivoted Column" = Table.Pivot(Table.TransformColumnTypes(#"Sorted Rows", {{"2022 Read date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows", {{"2022 Read date", type text}}, "en-US")[#"2022 Read date"]), "2022 Read date", "2022 Usage"),
    #"Sorted Rows1" = Table.Sort(#"Pivoted Column",{{"2023 Read Date", Order.Ascending}}),
    #"Pivoted Column1" = Table.Pivot(Table.TransformColumnTypes(#"Sorted Rows1", {{"2023 Read Date", type text}}, "en-US"), List.Distinct(Table.TransformColumnTypes(#"Sorted Rows1", {{"2023 Read Date", type text}}, "en-US")[#"2023 Read Date"]), "2023 Read Date", "2023 Usage"),
    #"Unpivoted Other Columns" = Table.UnpivotOtherColumns(#"Pivoted Column1", {"Customer", "Type", "Serial Number"}, "Attribute", "Value"),
    #"Renamed Columns" = Table.RenameColumns(#"Unpivoted Other Columns",{{"Attribute", "Date"}, {"Value", "Usage"}}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Renamed Columns",{{"Date", type date}}),
    #"Sorted Rows2" = Table.Sort(#"Changed Type1",{{"Date", Order.Ascending}})
in
    #"Sorted Rows2"

 

2.Try code as below to create a measure.

 

Day_Avg_Usage = AVERAGEX(FILTER(ALLSELECTED('Table'),'Table'[Date]=MAX('Table'[Date])),[Usage])

 

Result is as below.

 

Best Regards,

Yulia Yan

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.