Change from Start of every month

[paulfink]

hi guys,

i have a table that records the progress of projects every day, it has projectid, projectname, progress and progress date. I am trying to see what the change is since the start of the month for my visual that shows the last 6 months.

it should take the progress at the end of each month and take away progress from the start of the month.

e.g if progress is 50 at start of the month and 60 by the end = the change would be 10

Projectid	Projectname	progress	progress date
1	A	60	01/10/2021
2	B	40	01/10/2021
3	C	65	01/10/2021
1	A	72	02/10/2021
2	B	58	02/10/2021
3	C	80	02/10/2021

this is what my table looks like - it will record progress every day even if there is no change

Im assuming this will be from a measure so i want it to take the earliest date in the month and take it away from the latest date in the month and this should work for every month that the progress date has.

i need to put this in a column chart so we can see the total change in a month.

any ideas?

[amitchandak]

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

You can use openingbalancemonth and closingbalancemonth

example

https://www.youtube.com/watch?v=yPQ9UV37LOU

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[paulfink]

I have added sample data and desired output

[amitchandak]

@paulfink , Based on what I got so far , you need a month year column a measure like

CALCUALTE(lastnonblankvalue(Table[Date], sum(Table[Progress])) - firstnonblankvalue(Table[Date], sum(Table[Progress])) , allexpect(Table, Table[Projectid], Table[Month Year]))

[paulfink]

hi @amitchandak , this is only giving me the highest progress value currently - i will need 2 columns showing the value at the first date of the month for every month so sept, oct, nov, dec etc and another column that shows the last date of the month for every month. is this something you can do?

[v-yiruan-msft · Accepted solution]

Hi @paulfink ,

I created a sample pbix file(see attachment), please check whether that is what you want.

1. Create a date dimension table

2. Create a measure as below

Measure = 
VAR _seldate =  SELECTEDVALUE ( 'Date'[Date] )
VAR _mindate =
    CALCULATE (
        MIN ( 'Table'[progress date] ),
        ALLEXCEPT ( 'Table', 'Table'[Projectid] )
    )
VAR _minvalue =
    CALCULATE (
        SUM ( 'Table'[progress] ),
        FILTER ( 'Table', 'Table'[progress date] = _mindate )
    )
VAR _mindate2 =
    CALCULATE (
        MAX ( 'Table'[progress date] ),
        FILTER ( 'Table', 'Table'[progress date] < _seldate )
    )
VAR _curvalue =
    CALCULATE (
        SUM ( 'Table'[progress] ),
        FILTER ( 'Table', 'Table'[progress date] = _seldate )
    )
VAR _eqvalue =
    CALCULATE (
        SUM ( 'Table'[progress] ),
        FILTER ( 'Table', 'Table'[progress date] = _mindate2 )
    )
VAR _diff =
    IF ( ISBLANK ( _curvalue ), _eqvalue, _curvalue ) - _minvalue
RETURN
    IF ( _seldate < _mindate, BLANK (), _diff )

3. Create a column chart

In addition, you can create two calculated columns as below to get the start and end of every month:

sMonth = date(YEAR('Table'[progress date]),MONTH('Table'[progress date]),1)

eMonth = EOMONTH('Table'[progress date],0)

Best Regards