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
eheres
Frequent Visitor

repeat last record for future sales

Hi Everyone,

 

Hope you can help me, I got some units by month, sales manager and type of product. But the thing is that i need to repeat the last record by sales manager, i know that is only a dax formula that im doing wrong. Can you help me please

 

Here is the data, thats what i got

 

ProductSales Rep Sic Dec-20  Jan-21  Feb-21  Mar-21  Apr-21  May-21  Jun-21  Jul-21  Aug-21  Sep-21  Oct-21  Nov-21  Dec-21  Jan-22  Feb-22  Mar-22 
CXRMoira             -               -               -               -               -               -               -               -               -               -               -               -               -                 1             -               -  
CXRFernanda             -                 6               6               6               5               5               5               5               4               3               2               2               2             -               -               -  
CXRRaul               6             -               -               -               -               -               -               -               -               -               -               -               -               -               -               -  
CX20John                   25            25            25            25            25            25   
CX20Andre            25            25            25            25            25            25            25         

 

 

Thats what i need

ProductSales Rep Sic Dec-20  Jan-21  Feb-21  Mar-21  Apr-21  May-21  Jun-21  Jul-21  Aug-21  Sep-21  Oct-21  Nov-21  Dec-21  Jan-22  Feb-22  Mar-22 
CXRMoira             -               -               -               -               -               -               -               -               -               -               -               -               -                 1               1               1
CXRFernanda             -                 6               6               6               5               5               5               5               4               3               2               2               2             -               -               -  
CXRRaul               6             -               -               -               -               -               -               -               -               -               -               -               -               -               -               -  
CX20John                   25            25            25            25            25            25            25            25            25
CX20Andre            25            25            25            25            25            25            25             -               -               -               -               -               -               -               -               -  

 

Thank you!

1 ACCEPTED SOLUTION

Yes, I see. Sorry about that.

Please find link for last value. I had to rearrange the data to get in table form for Power BI.

https://drive.google.com/file/d/14-t-gm8lVliFbp9o8Ebphf6r9KJ2fgQj/view?usp=sharing 

 

Whitewater100_0-1648510958312.png

 

View solution in original post

6 REPLIES 6
Whitewater100
Solution Sage
Solution Sage

Hi:

It looks the same. If true you can duplicate the table either in DAX or Power Query.

 

For DAX go to Modeling> New Table 

New Table = 'First Table'

 

I hope this helps!

Hi Thank you, but they are not the same table, the last 2 months have amounts and the first one is not

Yes, I see. Sorry about that.

Please find link for last value. I had to rearrange the data to get in table form for Power BI.

https://drive.google.com/file/d/14-t-gm8lVliFbp9o8Ebphf6r9KJ2fgQj/view?usp=sharing 

 

Whitewater100_0-1648510958312.png

 

Thank you!! can you send me the dax? the google drive is not working

Hi:  Yes, DAX below.

I first loaded a table that looks like yours. Then in Power Query I did some steps. Here they are:

Whitewater100_0-1648511743279.png

 

This gave me a table that looks like this (4 columns)

Whitewater100_1-1648511799112.png

Total Amount = SUM(SalesTable[Amount])

Last Value = LASTNONBLANKVALUE(Dates[Date],[Total Amount])
 
if this answer is good for you, can you check it as solved? Thank you..

Where it says 'First Table' that is the name of your current table....

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