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
schoden
Post Partisan
Post Partisan

LastNONBLANKVALUE for each ID in each MONTH

Hi @amitchandak and Community, 

 

Sorry for for gap but I am struggling to get lastnonblankvalue for EACH ID in Each MONTH ?

For example in Jan 2020 there are two IDS - a and b. 

In Feb it populates from January for both a and b as 30 

In March a has 10 and b has 20 so total 30.

 

 

 

Will really appreciate. 

schoden_0-1617161014558.jpeg

 

 

10 REPLIES 10
Jihwan_Kim
Super User
Super User

Hi, @schoden 

Please check the below picture, if that is what you are looking for.

The sample pbix file 's link is down below.

 

Fix Total lastnonblankamount (month) =
VAR currentdate =
MAX ( dates[Date] )
VAR newtable =
ADDCOLUMNS (
CROSSJOIN ( VALUES ( dates[Month & Year] ), ALL ( 'Table'[ID] ) ),
"@last",
CALCULATE (
LASTNONBLANKVALUE ( 'Table'[Date], [Total Amount] ),
FILTER ( ALL ( dates ), dates[Date] <= currentdate )
)
)
RETURN
IF (
ISFILTERED ( 'Table'[ID] ),
CALCULATE (
LASTNONBLANKVALUE ( 'Table'[Date], [Total Amount] ),
FILTER ( ALL ( dates ), dates[Date] <= currentdate )
),
SUMX ( newtable, [@last] )
)

 

Picture2.png

 

https://www.dropbox.com/s/2sycbvbord3gfhc/schoden.pbix?dl=0 

 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Hi @Jihwan_Kim  Thank you for the response but How Can I filter by Status ? 

Hi, @schoden 

Thank you for your feedback.

Please correct me if I wrongly understand the situation in the report.

Please check the below picture if it is what you are looking for.

I understood that, if I include the status condition, each status will show the last update number, so the total number will be different than what I showed before.

Please also check the amended DAX measure and pbix file inside the link down below.

Picture1.png

 

 

https://www.dropbox.com/s/nl183z6mjp0rjen/schoden.pbix?dl=0  

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Result.JPG

 

Hi @Jihwan_Kim sorry for being unclear, and you are giving me pointers.

 

If you see the first table is the actual data.

The second table is what I am looking for outcome.

The Status also gets populated from previous value. The data stops after the status is closed. 

ID B stops at March 2020.

ID A stops at July 2020.

Hi, @schoden 

 

I tried it and found this problem. Since your main table doesn't have these rows, there is no way to add two rows in one month even if you add a calendar table. This is a limitation of dax.

3.png
I think the fastest way is that you manually added it yourself.

If you can understand what I said and change your requirement, please feel free to ask me.

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Yes @v-janeyg-msft  Yes like you pointed , if it is populated from the datasource only it will be much easier ,entering data/populating data in Power Bi can be difficult, in future hope microsoft allows it so it can work not as data analysistool but as well a functionality/capabilities like Data Source(mainly entering and structuring data) 

 

Hi, @schoden 

 

I think it's difficult, not powerbi doesn't allow it, but dax's operation mode doesn't support it. The data you want to get is more than one row and it is completely absent in the main table. It is impossible to get more than one row of data at one time. This is the problem. If the data and id in your main table are not omitted, it will always be two rows per month, then the problem is easy to handle. If you can understand what i said.

 

Best Regards

Janey Guo

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

ID B is closed on March 2020 , so its doenst appear after March 

ID A is closed after July 2020 , so it doesnt appear after July . 

amitchandak
Super User
Super User

@schoden , Create a column month year in table and try like

 

Month year = eomonth([Date],0)

 

Try meausre like

 

calculate(Sum(Table[amount]), filter(Table, Table[Date] = calculate(max(Table[date]), allexcept(Table, Table[ID], Table[Month year]))))

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

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.