Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
paulfink
Post Patron
Post Patron

Change from Start of every month

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

 

ProjectidProjectnameprogressprogress date
1A6001/10/2021
2B4001/10/2021
3C6501/10/2021
1A7202/10/2021
2B5802/10/2021
3C80

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?

1 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

yingyinr_0-1639385311670.png

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)

yingyinr_1-1639385410671.png

Best Regards

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

View solution in original post

5 REPLIES 5
amitchandak
Super User
Super User

@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.

I have added sample data and desired output

@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]))

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?

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

yingyinr_0-1639385311670.png

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)

yingyinr_1-1639385410671.png

Best Regards

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.