Getting Sum of Last Available Date in Month
Thursday, May 20th, 2021
Suppose you want to know how many items were purchased on the the last business day of the month. For example, here we see that 41 items were sold on the 30th of January, but no items were sold on the 31st.
You can use LastNonBlank to determine the last day of the month items were sold.
LastNonBlank = LASTNONBLANK('item purchased'[Date],sum('item purchased'[Quantity]))Creating a table with this expression, we see that 1/31 is not shown because there are no rows on this date. The view would also show 1/30 if there were rows for 1/31 with blank values for Quantity.
Now let’s use LASTNONBLANK to get the end of month total:
Quantity Last Day of Month = calculate(sum('item purchased'[Quantity]),LASTNONBLANK('item purchased'[Date],sum('item purchased'[Quantity])))
Sum of quantity sold on 1/30 was 41.