Forum Discussion

Chetan007's avatar
Chetan007
Frequent Visitor
2 years ago
Solved

Create Table Format For MIN Date First value and MAX Date Last Value

Data: Result: Case1: Group Min Date Max Date A 1220 1330   350+400+100+150+220=1220 300+550+80+250+150=1330   Date From 8-10 To 11-10     Case2: Group Min Date ...
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi Chetan007 

     

    Please try this:

    Here's the sample table:

    Table:

    Then add 2 measures:

    MinDate =
VAR _currentGroup =
    SELECTEDVALUE ( 'Table'[Group] )
VAR _vtable =
    ADDCOLUMNS (
        'Table',
        "_Mindate",
            CALCULATE (
                MIN ( 'Table'[Date] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Group] = EARLIER ( 'Table'[Group] )
                        && 'Table'[Category] = EARLIER ( 'Table'[Category] )
                )
            ),
        "_Maxdate",
            CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Category] = EARLIER ( 'Table'[Category] )
                        && 'Table'[Group] = EARLIER ( 'Table'[Group] )
                )
            )
    )
VAR _vtable_min =
    SUMMARIZE (
        FILTER ( _vtable, 'Table'[Date] = [_Mindate] ),
        'Table'[Group],
        "_Values", SUM ( 'Table'[Value] )
    )
RETURN
    MAXX ( FILTER ( _vtable_min, [Group] = _currentGroup ), [_Values] )

    MaxDate =
VAR _currentGroup =
    SELECTEDVALUE ( 'Table'[Group] )
VAR _vtable =
    ADDCOLUMNS (
        'Table',
        "_Mindate",
            CALCULATE (
                MIN ( 'Table'[Date] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Group] = EARLIER ( 'Table'[Group] )
                        && 'Table'[Category] = EARLIER ( 'Table'[Category] )
                )
            ),
        "_Maxdate",
            CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Category] = EARLIER ( 'Table'[Category] )
                        && 'Table'[Group] = EARLIER ( 'Table'[Group] )
                )
            )
    )
VAR _vtable_max =
    SUMMARIZE (
        FILTER ( _vtable, 'Table'[Date] = [_Maxdate] ),
        'Table'[Group],
        "_Values", SUM ( 'Table'[Value] )
    )
RETURN
    MAXX ( FILTER ( _vtable_max, [Group] = _currentGroup ), [_Values] )

    The result is as follow:

     

    Best Regards

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

bhanu_gautam's avatar
bhanu_gautam
Icon for Super User rankSuper User
2 years ago

Chetan007 , Try using 

 

 


Load your data into Power BI. Ensure your data has columns for Group, Date, and Value.


Create a new table to store the Min Date and Max Date for each group.

 

MinMaxDates =
SUMMARIZE(
YourTable,
YourTable[Group],
"Min Date", MIN(YourTable[Date]),
"Max Date", MAX(YourTable[Date])
)


Create measures to calculate the sum of values for the Min Date and Max Date.

MinDateSum =
CALCULATE(
SUM(YourTable[Value]),
FILTER(
YourTable,
YourTable[Date] = MIN(MinMaxDates[Min Date])
)
)



Create a final table to display the results.

FinalTable =
SUMMARIZE(
MinMaxDates,
MinMaxDates[Group],
"Min Date Sum", [MinDateSum],
"Max Date Sum", [MaxDateSum],
"Date Range", CONCATENATE("Date From ", CONCATENATE(MIN(MinMaxDates[Min Date]), CONCATENATE(" To ", MAX(MinMaxDates[Max Date]))))
)


Visualize the Data
Use the FinalTable to create a table visualization in Power BI. Add the columns Group, Min Date Sum, Max Date Sum, and Date Range to the table.

This will give you a table with the Min Date first value sum and Max Date last value sum for each group, along with the date range.

 

 

  • Chetan007's avatar
    Chetan007
    Frequent Visitor
    2 years ago

    when i use your suggestion the output is above.

    but i can't require this solution.

    My group is not fix i have multiple group. so i will give you multiple group example so please give me any solution.

    Data:

     

    Solution:

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi Chetan007 

       

      Please try this:

      Here's the sample table:

      Table:

      Then add 2 measures:

      MinDate =
VAR _currentGroup =
    SELECTEDVALUE ( 'Table'[Group] )
VAR _vtable =
    ADDCOLUMNS (
        'Table',
        "_Mindate",
            CALCULATE (
                MIN ( 'Table'[Date] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Group] = EARLIER ( 'Table'[Group] )
                        && 'Table'[Category] = EARLIER ( 'Table'[Category] )
                )
            ),
        "_Maxdate",
            CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Category] = EARLIER ( 'Table'[Category] )
                        && 'Table'[Group] = EARLIER ( 'Table'[Group] )
                )
            )
    )
VAR _vtable_min =
    SUMMARIZE (
        FILTER ( _vtable, 'Table'[Date] = [_Mindate] ),
        'Table'[Group],
        "_Values", SUM ( 'Table'[Value] )
    )
RETURN
    MAXX ( FILTER ( _vtable_min, [Group] = _currentGroup ), [_Values] )

      MaxDate =
VAR _currentGroup =
    SELECTEDVALUE ( 'Table'[Group] )
VAR _vtable =
    ADDCOLUMNS (
        'Table',
        "_Mindate",
            CALCULATE (
                MIN ( 'Table'[Date] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Group] = EARLIER ( 'Table'[Group] )
                        && 'Table'[Category] = EARLIER ( 'Table'[Category] )
                )
            ),
        "_Maxdate",
            CALCULATE (
                MAX ( 'Table'[Date] ),
                FILTER (
                    ALLSELECTED ( 'Table' ),
                    'Table'[Category] = EARLIER ( 'Table'[Category] )
                        && 'Table'[Group] = EARLIER ( 'Table'[Group] )
                )
            )
    )
VAR _vtable_max =
    SUMMARIZE (
        FILTER ( _vtable, 'Table'[Date] = [_Maxdate] ),
        'Table'[Group],
        "_Values", SUM ( 'Table'[Value] )
    )
RETURN
    MAXX ( FILTER ( _vtable_max, [Group] = _currentGroup ), [_Values] )

      The result is as follow:

       

      Best Regards

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

      • Chetan007's avatar
        Chetan007
        Frequent Visitor
        2 years ago

        Thank You so much for Your Suggestion