Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
JustinDoh1
Post Prodigy
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.

JustinDoh1_0-1646524614600.png

 

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: 

JustinDoh1_0-1646524062116.png

 

JustinDoh1_1-1646524128905.png

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

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

 

 

 

 

View solution in original post

8 REPLIES 8
JustinDoh1
Post Prodigy
Post Prodigy

@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.

JustinDoh1_0-1647022765590.png

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.

JustinDoh1_1-1647022927835.png

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

JustinDoh1_3-1647023574895.png

 

Thank you for your help again!

 

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

 

 

 

 

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

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.

@lbendlin Thank you so much! You are awesome.

lbendlin
Super User
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.

@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:

JustinDoh1_0-1646764445958.png

 

JustinDoh1_1-1646763589757.png

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.

JustinDoh1_2-1646763737460.png

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

JustinDoh1_3-1646763910675.png

Thank you.

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.