Usage forecast DAX

[KristyP]

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 line in the matrix.

I have two points of usage so was planning to calculate average usage and add this to the current ussage to build the forecast. Can be daily or monthly average. Data looks like the below:

Thank you in advance for your assistance.

[v-weiyan1-msft]

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.

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[KristyP]

Hi,

https://docs.google.com/spreadsheets/d/1xvNJTyI9NCGBF1A8as9FgcuwBezPH8LI/edit?usp=drive_link&ouid=10... 

Data to be presented like the below: