View Report As of (Certain Month)

[JustinDoh1]

I am sharing my Pbix file (attached).

Update: This is a link to the updated Pbix file as well (responding to the bottom thread):

I am working on an AR Report which requires looking at numbers "As of" certain month.

My previous worker came accross creating this logic with using this measure to select certain month as shown below, and I think this is fine (as far I could tell).

[Selected Month Measure] =

CALCULATE(

                     MAX(MonthsTable[Month Start Day]),

                     ALLSELECTED(MonthsTable)

)

Then next process is using this measure ([Selected Month Measure]) and applying into series of other measures for the bucket of periods (example: Current, 30-59 (days), 60-89 (days).. 210+ (days) etc.) to display different numbers based on the selection of month:

To me, these measures look little bit cumbersom/lengthy.

For example: 

Currently, it works, but with current datasets (Calendar & Data tables), is there a better way to create measures of different time buckets? 

Thank you.

[JustinDoh1]

@lbendlin 

First, Thank you so much for your help and sample of codes.

Let me share image first to illustrate my further questions on this.

Bottom is original.

What I am struggling is we are using two different dates (EffectiveDate and TransactionDate).

Also, we have >=, < etc.

How do we consider these?

This is modified version, but I don't think it is good enough.

I am sharing my updated Pbix file here.

I also need to create relationship with "MonthsTable", but where/how?

Thank you for your help again!

[lbendlin · Accepted solution]

You don't want a relationship involving the MonthsTable. It needs to stay disconnected.

30-59 NEW = 
CALCULATE(SUM(Data[Amount]), 
    Data[EffectiveDate]>= selectedvalue(MonthsTable[Previous Month First Day]),
    Data[EffectiveDate]< selectedvalue(MonthsTable[Current Month First Day]),
    Data[TransactionDate]< selectedvalue(MonthsTable[Next Month First Day])
)

[JustinDoh1]

@lbendlin Sorry. I have a question. Why does the relationship (with MonthTable) has to be disconnected? Thanks.

[lbendlin]

Its only purpose is to feed the slicer. It should not actively filter any of the other tables.  All filtering is done via the measure logic.

[JustinDoh1]

@lbendlin Thank you so much! You are awesome.

[lbendlin]

If you move the "x months ago first day" calculated columns into the MonthsTable then you can get rid of all the LOOKUPVALUE stuff.

[JustinDoh1]

@lbendlin 

First, thank you so much for your help.

I revised Pbix file and added three calculated columns into the MonthsTable.

This is Google drive link for the updated Pbix file since I was not able to upload a file after the original post.

I made the visual pretty simple as shown below:

My question is how do I create relationship with these columns (MonthsTable) to measures in 'Data' table, possibly revise measures and make the visual works as what we have.

Also, other objective is getting rid of 'Lookupvalue' inside these measures ('30-59','60-89' & 'Current').

Thank you.

[lbendlin]

Here's an example.  Correct the timing as needed. change the relationship as needed (if you need EffectiveDate rather than TransactionDate)

60-89 = CALCULATE(SUM(Data[Amount]), 
DATESBETWEEN('Calendar'[Date],selectedvalue(MonthsTable[2 Months Ago First Day]),selectedvalue(MonthsTable[Previous Month First Day])))