Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Repeat till FY

Hi, Please help with below requirement. In the below dump   From the above raw dump,  created a new table with total revenue per Cutsomer as below:       Hence the Toatl Revenue f...
Need Help
  • v-diye-msft's avatar
    v-diye-msft
    6 years ago

    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"

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
6 years ago

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/