Search code examples
powerbidaxpowerquerypowerbi-desktoppowerbi-custom-visuals

How to create chart with MTD and Eomonth value in power BI

The chart I created already has all the EOM and MTD values, now I 'd like to just show/filter MTD & EOM value with x - axis is the last working day of the month (I mean only show the circled in red in the image below).

MTD value is the number of new deposits accumulated at the end of the month, and EOM value is the number of outstanding customer deposits each month. I created 1 measure MTD: New outstanding amount MTD = TOTALMTD(SUM(Data[OS]), Data[Date])

enter image description here

I don't want to filter day by day like the picture, is there a better way? enter image description here

Solution

  • DAX time-intelligence functions allow you to manipulate the time period over which an expression is calculated.

    DATESBETWEEN() returns all dates between two inputs. When the first date input is set to BLANK(), the output date range will begin at the earliest date.

    Prior Week Percentage of missing product = 
CALCULATE(
    SUM(Data[OS]),
    DATESBETWEEN( 'Calendar'[Date], BLANK(), LASTDATE( 'Calendar'[Date] ) )
)

    This returns the sum of all outstanding deposits from the beginning of time to the last date in context.

    ---EDIT-----------------------

    Per OP edit, you will need a month column on your date table to aggregate by month. Your MTD measure will continue to work, and this measure will continue to work. One thing I've learned about creating month columns is that it is best to use the first day of the month (EOMONTH( 'Calendar'[Date], -1 ) + 1). Using the last day of the month aligns the data point so close to the label for the first day of the following month that it can make your chart difficult to read.

    ---EDIT 2---------------------

    To find the value just on the last day of the month, you can just use the LASTDATE() function alone.

    CALCULATE(
    SUM(Data[OS]),
    LASTDATE( 'Calendar'[Date] )
)

    This will find the last date in context, and return the value only on that day. As long as your visual is summarizing by month, this will work.