Forum Discussion
Repeat till FY
Hi,
Please help with below requirement.
In the below dump
Raw Dump
From the above raw dump, created a new table with total revenue per Cutsomer as below:
Hence the Toatl Revenue for Customer C for june is 1200(300+800+100) and the amount should be projected as 1200 till Mar-21 ( Financial Year is from Apr20-Mar-21) like wise for all customers. Attached sample data for your reference.
https://drive.google.com/file/d/1vaVAYIryf_l7HfctNEGTF5qXSurjNCXR/view?usp=sharing
Please let me know if you have any questions.
Regards,
Chandra
Hi Anonymous
You can use the power query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ30TcyUNJRcgRiUwMDpVgdqLgpRNwJiM2Qxc0g4s5AbIwsjmSOoSl2c4xwmGOBwxxLHO4xxWGOIUg8FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Month = _t, Customer = _t, Revenue = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Customer", type text}, {"Revenue", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Month", "Customer"}, {{"Count", each List.Sum([Revenue]), type number}}) in #"Grouped Rows"
2 Replies
Anonymous , try with a date calendar
calculate(sum(Table[Revenue]),DATESYTD('Date'[Date]),"3/31")
In case your month in give format
Date = "01-"& [Month] // and mark as date
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/Hi Anonymous
You can use the power query:
let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwtNQ30TcyUNJRcgRiUwMDpVgdqLgpRNwJiM2Qxc0g4s5AbIwsjmSOoSl2c4xwmGOBwxxLHO4xxWGOIUg8FgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Month = _t, Customer = _t, Revenue = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"Month", type date}, {"Customer", type text}, {"Revenue", Int64.Type}}), #"Grouped Rows" = Table.Group(#"Changed Type", {"Month", "Customer"}, {{"Count", each List.Sum([Revenue]), type number}}) in #"Grouped Rows"
