Forum Discussion

Ashik008's avatar
Ashik008
Frequent Visitor
3 years ago
Solved

Previous day count

Hi folks ,

Thanks for the help earlier . I need one more help.

I have ticket tracker in excel .

record added daterequest numbertype
7/1/2022ab123A
7/1/2022qw123A
12/13/2022we345B
12/14/2022rt567A

 

I need to  create a view in which it shows

TypeTotal countprevious day count
A32
B1 
   

please help

  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Ashik008 ,

    You can create the measures as below to get it, please find the details in the attachment.

    Total count = COUNT('Table'[request number])
    previous day count = 
    VAR _seldate =
        SELECTEDVALUE ( 'Table'[record added date] )
    VAR _seltype =
        SELECTEDVALUE ( 'Table'[type] )
    VAR _predate =
        CALCULATE (
            MAX ( 'Table'[record added date] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[type] = _seltype
                    && 'Table'[record added date] < _seldate
            )
        )
    RETURN
        CALCULATE( 
         [Total count],
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[type] = _seltype
                    && 'Table'[record added date] = _predate
            )
        )

    If the above one can't help you get the desired result, please provide some sample data in your tables  (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    Hi Ashik008 ,

    You can create the measures as below to get it, please find the details in the attachment.

    Total count = COUNT('Table'[request number])
    previous day count = 
    VAR _seldate =
        SELECTEDVALUE ( 'Table'[record added date] )
    VAR _seltype =
        SELECTEDVALUE ( 'Table'[type] )
    VAR _predate =
        CALCULATE (
            MAX ( 'Table'[record added date] ),
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[type] = _seltype
                    && 'Table'[record added date] < _seldate
            )
        )
    RETURN
        CALCULATE( 
         [Total count],
            FILTER (
                ALLSELECTED ( 'Table' ),
                'Table'[type] = _seltype
                    && 'Table'[record added date] = _predate
            )
        )

    If the above one can't help you get the desired result, please provide some sample data in your tables  (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

    How to upload PBI in Community

    Best Regards

  • Hello Ashik008 ,

    Previous Day Qty =
    CALCULATE(SUMX(TrainingSample2,TrainingSample2[OrderQty]),PREVIOUSDAY(TrainingSample2[SalesDate]),ALLEXCEPT(TrainingSample2,TrainingSample2[SalesDate]))