Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Repeat till FY

Hi,

Please help with below requirement.

In the below dump

 

Raw DumpRaw Dump

From the above raw dump,  created a new table with total revenue per Cutsomer as below:

 

image.png

 

 

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

1 ACCEPTED SOLUTION
v-diye-msft
Community Support
Community Support

Hi @Anonymous 

 

You can use the power query:

2.PNG

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"

3.PNG

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

View solution in original post

2 REPLIES 2
v-diye-msft
Community Support
Community Support

Hi @Anonymous 

 

You can use the power query:

2.PNG

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"

3.PNG

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.
amitchandak
Super User
Super User

@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/

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors