cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Post Prodigy

## View Report As of (Certain Month)

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.

1 ACCEPTED SOLUTION
Super User

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

8 REPLIES 8
Post Prodigy

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!

Super User

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

Post Prodigy

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

Super User

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.

Post Prodigy

@lbendlin Thank you so much! You are awesome.

Super User

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

Post Prodigy

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.

Super User

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

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors