Value on the first day and last day of the month

[Gjakova]

Hi there, I need to calculate the revenue of each month. To be specific, it is not a month in general but the first day of the month (for example: September 1st, 2021).

So that would be measure A.

Measure B would be calculating the value of the last day of the month (September 30th, 2021) orthe first day of next month (October 1st, 2021).

Once I got measure A & B, I need to drop them in a matrix table which looks like this:

Product	Date	Measure A	Revenue Avg. month	Measure B
Alpha	September 1st, 2021	20.5	21	24
Alpha	October 1st, 2021	24	13	8
Alpha	November 1st, 2021	8	30	100

I assume I need to work with CALCULATE + the filter function, but I can't get it to work. Hope someone can push me in the right direction!

[v-yangliu-msft · Accepted solution]

Hi  @Gjakova ,

I created some data:

Here are the steps you can follow：

1. Create calculated column.

Date1 = DATE(YEAR('Table'[date]),MONTH('Table'[date]),1)

And convert the format:

2. Create measure.

MeasureA = CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[date]=DATE(YEAR(MAX('Table'[date])),MONTH(MAX('Table'[date])),1)&&'Table'[Product]=MAX('Table'[Product])))

MeasureB =
var _1=
CALCULATE(SUM('Table'[amount]),FILTER(ALL('Table'),'Table'[date]=DATE(YEAR(MAX('Table'[date])),MONTH(MAX('Table'[date]))+1,1)&&'Table'[Product]=MAX('Table'[Product])))
return
IF(_1 = BLANK(),CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[date]=EOMONTH('Table'[date],0)&&'Table'[Product]=MAX('Table'[Product]))),_1)

3. Result:

MeasureA: for the beginning of the month

MeasureB: When there is a value at the beginning of the next month, it is the value of this month, if there is no value at the beginning of the next month, it is the value at the end of the month

Best Regards,

Liu Yang

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

[edhans]

@Gjakova - we really need a bit more info. For example, "Measure B would be calculating the value of the last day of the month (September 30th, 2021) orthe first day of next month (October 1st, 2021)." Which is it? the last day of current month or first of next? I can return one or the other, but not both.

You've also given us no actual data. You started with Measure A results, so no source data.

That said, this logic may help:

Measure 1 = 
var varSelectedDate = SELECTEDVALUE('Date'[Date])
VAR varDate = EOMONTH(varSelectedDate,-1) + 1
VAR Result = 
    CALCULATE(
        SUM('Table'[Measure A]),
        KEEPFILTERS('Date'[Date] = varDate)
    )
RETURN
Result

Using EOMONTH, I took the current date, moved back to the last day of the previous month, then added a day, so Sept 1 through Sept 30 will always go back to August 31, then add a day and arrive back at Sept 1. You can manipulate that formula. You could use EOMONTH(varSelectedDate,0) + 1. Sept 15 would return Oct 1 in that case.

You need a date table too. I have one here. Creating a Dynamic Date Table in Power Query
Lastly, here is my PBIX file, complete with a date table and the measures I used. 

But if you want a more comprehensive answer, we need more info and definitely more data. Remember, you know your data very well. We know nothing. Be very explicit with the requirements. Thanks!

How to get good help fast. Help us help you.

How To Ask A Technical Question If you Really Want An Answer

How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[Gjakova]

Hi @edhans, thank you for your reply, I tried your measure but it did not work. Here is a bit more explanation:

Basically it looks like an Opening and Closing balance. 

So perhaps the example is not that great, but the reason that I said that measure B could either be the last day of the current month or the first day of the next month, is because their value should ALWAYS be the same. Since Closing Balance of the last day of the month, is the Opening Balance on the first day of the month.

Measure A basically needs to ALWAYS give me what the value was on the first day of the CURRENT month that I am in on the table. So if my table looks like:

Product	Date	Measure A	Measure B
Samsung	September, 2020 (only first day of the month)	80 (value on September 1st)	100 (Opening balance on October 1st or Closing Balance on September 31 (result should be the same))
Samsung	October, 2020 (only first day of the month)	100 (value on October 1st)	40 (opening value on November 1st or closing value on October 31)
Samsung	November, 2020 (only first day of the month)	40 (value on November 1st)	80 (opening value on December 1st or closing balance on November 31)

Hope this is a bit more clear.

[edhans]

No, it really isn't. Maybe someone else understand where you are going. They say a picture is worth 1,000 words. For me, some tables with data is worth more than several paragraphs of explanation. You've still provided no data, just measure results you expect me to write to calculate over data I've not seen.