Completed Month measures

DAX DAX Calculations
1

Currently, I have these measures. Actuals Month show the results from the prior month, not the current. Actuals QTR to Date show Oct and Nov if currently in Dec. YTD Actuals show Jan - Nov if currently in Dec.

Now, I need to give the user a QtrYear filter, so they can look back at prior quarters. I would need these three measures to change with the users selection of QtrYear. So, if they choose 3rd quarter of 2020, the Actuals Month would display September(last completed month of quarter), Actuals QTR to Date would show the entire 3rd quarter, and the YTD Actuals would show Jan - September. Any suggestions?

Actuals Month =
CALCULATE(
[Net Debit / Credit ($)]*-1,
YEAR(ā€˜Posting Periodā€™[*Date (period)]) = YEAR(TODAY()),
MONTH(ā€˜Posting Periodā€™[*Date (period)]) = MONTH(TODAY())-1)

Actuals QTR to Date =
VAR Quarter1 = IF( MONTH(TODAY()) = 1, 4, IF(MONTH(TODAY()) in {4,7,10}, QUARTER(TODAY())-1, QUARTER(TODAY())))
VAR Year1 = IF(MONTH(TODAY()) = 1, YEAR(TODAY())-1,YEAR(TODAY()))
VAR Month1 = IF(MONTH(TODAY()) = 1, 13, MONTH(TODAY()))
RETURN
CALCULATE(
[Net Debit / Credit ($)]*-1,
YEAR(ā€˜Posting Periodā€™[*Date (period)]) = Year1 ,
MONTH(ā€˜Posting Periodā€™[*Date (period)]) < Month1 ,
QUARTER(ā€˜Posting Periodā€™[*Date (period)]) = Quarter1)

YTD Actuals =
VAR Year1 = IF(MONTH(TODAY()) = 1, YEAR(TODAY())-1,YEAR(TODAY()))
VAR Month1 = IF(MONTH(TODAY()) = 1, 13, MONTH(TODAY()))
RETURN
CALCULATE(
[Net Debit / Credit ($)]*-1,
YEAR(ā€˜Posting Periodā€™[*Date (period)]) = Year1,
MONTH(ā€˜Posting Periodā€™[*Date (period)]) < Month1)

2

Hi @Usates,

Where youā€™re using TODAY() replace that with the last ā€œvisible dateā€ like so MAX( Dates[Date] )
You might also want to store that LastDateInSelection in a variable and call that.

I hope this is helpful.
Should you need further assistance provide a work in progress PBIX file.

2 Likes
3

Hi @Usates, did the response provided by @Melissa help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!

4

Sorry for the delay, slight variation to my original request. I need the user to have a QTR/YEAR slicer. For the [Actuals Month] measure, if we are in the current quarter/year(slicer set to 4QTR2020), I need it to display last months total. If the user picks any previous qtr, I need this measure to return nothing(blank). How do I do this?

Currently the measure is this:
Actuals Month =
CALCULATE(
[Net Debit / Credit ($)]*-1,
YEAR(ā€˜Posting Periodā€™[*Date (period)]) = YEAR(TODAY()),
MONTH(ā€˜Posting Periodā€™[*Date (period)]) = MONTH(TODAY())-1)

5

Hi @Usates.

To help us further analyze your current state and visualize your issue, could you please provide as many as you can of:

  • Your work-in-progress PBIX file, using sanitized data if necessary
  • Your dataset as an Excel file (again, sanitized if necessary)
  • A detailed mock-up (marked-up screenshot or Excel file) of your desired outcome.

Also, if you provide DAX in your post, please format it using the built-in formatter.

Greg

_eDNA Forum - Format DAX

6

Sorry, Iā€™m trying to figure out how to post dax correctly. I think I was able to figure out what I needed with the help from Melissaā€™s post. Thank you