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
Anonymous
Not applicable

Calculated field value from max selected date

Hi Team,

 

I have condition to categorize the aging of Invoice

if (invoice[Aging]>120,"> 120",
if (invoice[Aging]>90 && invoice[Aging]<=120, "90-120",
if (invoice[Aging]>60 && invoice[Aging]<=90, "60-90",
if (invoice[Aging]>30 && invoice[Aging]<=60, "30-60",
if (invoice[Aging]>0 && invoice[Aging]<=30, "1-30",
"Not Overdue" )))))

and the formula of aging is number of days start from DueDate to ReportingDate that selected by user as picture, the issue is the value always return Max Reporting Date in the calendar table (31/12/2021).

image.png

 
 

Any insight and assistance would be greatly appreciated. Thank you

 

Regards,

Agus

 

 

  

10 REPLIES 10
RondaHill
Regular Visitor

I am working on a similar issue. I created a table called Selected Dates. We are only looking back at the past 60 days.

 

SelectedDates = CALENDAR(TODAY()-60, TODAY())
 
I renamed the resulting column to be Report Date. This is the field you use in your slicers.
 
I then have a measure called SelectedReportDate.
 
SelectedReportDate = SELECTEDVALUE(SelectedDates[Report Date], TODAY())
 
This is the field you use in your other measures.
 
Hope this helps.
v-easonf-msft
Community Support
Community Support

Hi , @Anonymous 

Can you show me the relationship between your calendar table and fact table?

In addition, please pay attention to the data type and  summarization of your field "ReportDate".

37.png

 

Best Regards,
Community Support Team _ Eason

 

 

Anonymous
Not applicable

Hi @v-easonf-msft 

Thanks for you response and attached the table relations and you can download my pbix file https://1drv.ms/u/s!Aih1MRphMRei21KYxISSSRNhWlK9?e=3wtTzH 

 

pbi_aging1.png

 

@RondaHill I had try with column but therresult of date always today but if I tried with measure the date result is latest of selected date but the issue in chart only shown I bar chart as picture how the differents

 

pbi_aging2.png    

Anonymous
Not applicable

HI All,

 

I still did not get solution, any help appreciated.

 

Thanks & Regards,

Hi , @Anonymous 

Sorry for late reply.

It seems that you just need to  drag the measure "m_report date"  to "values",then you will get what you want ...

 

38.png

 

Best Regards,
Community Support Team _ Eason

Anonymous
Not applicable

Hi @v-easonf-msft 

 

I am agreed with you putting m_report date"  to "values" will be display correctly in table but still incorrect for aging calculation 

 

Regards,

supriatna

Hi , @Anonymous 

Once the calculated column is created, its results in the table  will not be affected  no matter how you change the slicer value .

So all new data fields  that need to be affected by the slicer should be created with measure rather than calculated column. 

 

In fact,  I think  you already got the results you want. The two correct visual on the upper left and lower right that you have kept is exactly what you want.

 

 

45.png

 

If I misunderstand,please feel free to let me know.

 

Best Regards,
Community Support Team _ Eason

 

Anonymous
Not applicable

agingCalculated.jpg

 Hi @v-easonf-msft 

 

Many thanks for your response

 

For my condition m Aging calculated measures is the correct one (in red colour) since the aging calculated base on the latest selected date, you can see the different result between c Aging (today) and m Aging (lates selected date).

 

The problem is when m Aging Cat put in Bar Chart only shows one bar (>120 ). I don't know why the field m Aging Cat can't put in the axis of the bar chart. 

 

Regards,

Supriatna

amitchandak
Super User
Super User
Anonymous
Not applicable

@amitchandak thank you for your respon 
Segmentation or binning could be used after the aging value is correct, the problem is not grouping but how to get aging value based on last date of selected reporting date

 

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.