Forum Discussion

Amz_123's avatar
Amz_123
Helper I
6 years ago
Solved

Power BI DAX

Hi Community, Assist me in finding DAX to find Frequency of Item Returned back in a store. My Data Looks like this:     In my Case, I have to find at what frequency an item is returned bac...
Data Protection
General Comment
Interesting Links
Show and Tell
Tips and Tricks
Tutorial Requests
  • Mariusz's avatar
    Mariusz
    6 years ago

    Hi Amz_123 

     

    You can try Measure like this.

    Measure = 
VAR __allexcept = ALLEXCEPT( 'Table', 'Table'[Item] ) 
VAR __sold = CALCULATETABLE( VALUES( 'Table'[Date] ), 'Table'[Status] = "Sold", __allexcept )
VAR __tbl = 
    GENERATE(
        __sold,
        VAR __date = 'Table'[Date]
        RETURN 
        CALCULATETABLE( 
            SELECTCOLUMNS( 
                'Table', 
                "@Date", 'Table'[Date] 
            ), 
            'Table'[Type] = "Return", 
            'Table'[Date] > __date,
            __allexcept 
        ) 
    )
RETURN 
SUMX( __tbl, INT(  [@Date] - 'Table'[Date] ) ) / SUMX( __tbl, 1 )

     

    Best Regards,
    Mariusz

    If this post helps, then please consider Accepting it as the solution.

     

Mariusz's avatar
Mariusz
Community Champion
6 years ago

Hi Amz_123 

 

You can try Measure like this.

Measure = 
VAR __allexcept = ALLEXCEPT( 'Table', 'Table'[Item] ) 
VAR __sold = CALCULATETABLE( VALUES( 'Table'[Date] ), 'Table'[Status] = "Sold", __allexcept )
VAR __tbl = 
    GENERATE(
        __sold,
        VAR __date = 'Table'[Date]
        RETURN 
        CALCULATETABLE( 
            SELECTCOLUMNS( 
                'Table', 
                "@Date", 'Table'[Date] 
            ), 
            'Table'[Type] = "Return", 
            'Table'[Date] > __date,
            __allexcept 
        ) 
    )
RETURN 
SUMX( __tbl, INT(  [@Date] - 'Table'[Date] ) ) / SUMX( __tbl, 1 )

 

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.