Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

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

 
 

Any insight and assistance would be greatly appreciated. Thank you

 

Regards,

Agus

 

 

  

Need Help

10 Replies

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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

       

  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    6 years ago

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

     

    Best Regards,
    Community Support Team _ Eason

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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 

       

       

      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

       

          

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        HI All,

         

        I still did not get solution, any help appreciated.

         

        Thanks & Regards,

  • RondaHill's avatar
    RondaHill
    Regular Visitor
    6 years ago

    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.