Forum Discussion

Chetan007's avatar
Chetan007
Frequent Visitor
1 year ago
Solved

Create table format for min and max Date wise Value

Data                                                    

 

    

  Expected Result:

Case 1:

categoryMin DateMax Date
A37.539
 From 1-10 To 4-10 

     

Case 2:

categoryMin DateMax Date
A3955
 From 4-10 To 10-10 

Case3:

categoryMin DateMax Date
A6055
 From 6-10 To 10-10 

 

Date column is also added in slicer so when i change the date then automatically change the value..

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Chetan007 ,

     

    You need to create a date table and then try formula like below to create measure:

    Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

    MinValue = 
CALCULATE(
    MIN('Table'[Value]), 
    FILTER(
        'Table', 
        'Table'[Date] >= MIN('Date'[Date]) && 'Table'[Date] <= MAX('Date'[Date])
    )
)
    MaxValue = 
CALCULATE(
    MAX('Table'[Value]), 
    FILTER(
        'Table', 
        'Table'[Date] >= MIN('Date'[Date]) && 'Table'[Date] <= MAX('Date'[Date])
    )
)
    DateRange = 
"From " & FORMAT(MIN('Date'[Date]), "MMMM d") & " To " & FORMAT(MAX('Date'[Date]), "MMMM d")


    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

3 Replies

  • PhilipTreacy's avatar
    PhilipTreacy
    Icon for Super User rankSuper User
    1 year ago

    Chetan007 

     

    Download PBIX file with example below

     

    These work for me

     

     

    Min Value = CALCULATE(MIN('DataTable'[Value]), FIRSTNONBLANK('DataTable',('DataTable'[Value])))
    Max Value = CALCULATE(MAX('DataTable'[Value]), LASTNONBLANK('DataTable',('DataTable'[Value])))

     

     

     

     

     

     

    Regards

     

    Phil

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Chetan007 ,

     

    You need to create a date table and then try formula like below to create measure:

    Date = CALENDAR(MIN('Table'[Date]),MAX('Table'[Date]))

    MinValue = 
CALCULATE(
    MIN('Table'[Value]), 
    FILTER(
        'Table', 
        'Table'[Date] >= MIN('Date'[Date]) && 'Table'[Date] <= MAX('Date'[Date])
    )
)
    MaxValue = 
CALCULATE(
    MAX('Table'[Value]), 
    FILTER(
        'Table', 
        'Table'[Date] >= MIN('Date'[Date]) && 'Table'[Date] <= MAX('Date'[Date])
    )
)
    DateRange = 
"From " & FORMAT(MIN('Date'[Date]), "MMMM d") & " To " & FORMAT(MAX('Date'[Date]), "MMMM d")


    Best Regards,
    Adamk Kong

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

     

     

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    1 year ago

    Hi,

    Create a Calendar Table and a relationship (Many to One and Single) from the Date column of the Fact Table to the Date column of the Calendar Table.  To your slicer, drag Date from the Calendar table and select any date range.  Write these measures

    Total = sum(Data[Value])

    Total on min date = calculate([Total],datesbetween(calendar[date],min(calendar[date]),min(calendar[date])))

    Total on max date = 

    calculate([Total],datesbetween(calendar[date],max(calendar[date]),max(calendar[date])))

    Hope this helps.