Power BI table help

[dtango9]

Hello,

I created a table visual that I'm hoping someone can help me with.  The columns for the table are:  Expense, MTD Actual, MTD Budget, PY (Prior Year), and Budget Variance.  The problem I'm having is that the MTD Actual and MTD Budget columns are pulling, for instance, all October results but for every year instead of just 2020.  I can't use a filter on the page or visual because one of the columns is Prior Year.  The data is always used for the previous month, ie:  we are currently in November so I would be reporting on October's results.

Can someone help me with the correct formulas for:

MTD Actual (2020 + Previous Month)

MTD Budget (2020 + Previous Month)

Prior Year (2019 + Previous Month)?

Thank you!

[V-lianl-msft · Accepted solution]

Hi @dtango9 ,

Try to create measure like this:

MTD Actuals =
CALCULATE (
    SUM ( financials[ Sales] ),
    FILTER (
        financials,
        financials[Date]
            >= EDATE ( DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 ), -1 )
            && financials[Date] < DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
    )
)

Sample .pbix

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

[Ashish_Mathur]

Hi,

Share some data and show the expected result.  If possible, share an MS Excel workbook as well with your formulas so that the logic can be understood and converted into the DAX formula language.

[dtango9]

*** I should also note that our fiscal year begins in November and ends in December.

MTD Actuals = CALCULATE( SUM('finance ExpensesWithAccountDescriptions'[Total Amount]))

YTD Actuals = CALCULATE( [Actuals],DATESYTD('New Calender Table'[Dates], "30/11"))

MTD Budget = CALCULATE(SUM('FY2020 Budget'[Mo. Budget]))

YTD Budget = CALCULATE( [Budget],DATESYTD('New Calender Table'[Dates], "30/11"))

MTD Prior Year = CALCULATE(SUM('finance ExpensesWithAccountDescriptions'[Total Amount]),DATEADD('New Calender Table'[Dates],-1,YEAR))

YTD Prior Year = CALCULATE( [Prior Year],DATESYTD('New Calender Table'[Dates], "30/11"))

[Ashish_Mathur]

Hi,

Not much to suggest.  Ensure that Year/Quarter/Month/Date are dragged from the Calendar Table.  Also, YTD actuals should be

YTD Actuals = CALCULATE( [MTD Actuals],DATESYTD('New Calender Table'[Dates], "30/11"))

My assumption is that all relationships are properly set up.