Forum Discussion

rodrigo_avf's avatar
rodrigo_avf
Frequent Visitor
3 years ago

Recontact count using DAX

Hi, hope someone can help me with this. I have a 2 columns table with Date and Tickets count. I need to add a new column using DAX (can also be a measure instead) that will give me the recontact co...
Need Help
barritown's avatar
barritown
Solution Sage
3 years ago

Hi rodrigo_avf,

I think I've solved your problem via an additional table. If you check and confirm that the result on your working data is fine, I will be able to pack this table into a calculated column or a measure (another option - to create three additional columns but use only the final one). 

 

 

AugmentedTable = 
ADDCOLUMNS ( 
    ADDCOLUMNS ( 
        ADDCOLUMNS ( data, 
                    "Draft", 
                    VAR StartDate = [Date]
                    VAR EndDate = StartDate + 7
                    RETURN SUMX ( FILTER ( data, [Date] >= StartDate && [Date] <= EndDate ), [Tickets] ) - 1 ),
        "Flag",
        VAR CurrentDate = [Date]
        VAR PreviousDate = CurrentDate - 7
        VAR ExistingPreviousDate = MINX ( FILTER ( data, [Date] < CurrentDate && [Date] >= PreviousDate ), [Date] )
        RETURN IF ( MINX ( FILTER ( data, [Date] = ExistingPreviousDate ), [Draft] ) > 0, 1, 0 ) ),
    "Result",
    IF ( [Flag] = 0, [Draft], 0 ) )

Best Regards,

Alexander

My YouTube vlog in English

My YouTube vlog in Russian

rodrigo_avf's avatar
rodrigo_avf
Frequent Visitor
3 years ago

Hi barritown 
Thank you for taking the time to try and help me.
Unfortunatelly that did not solve the problem.
Here is a screen shot of the results, notice that it seems to work up to day 10/04 but afterwards it doesn't, the flag isn't correct. On the 18/04, 26/04, 05/05 the result should not have been 0.

 

You'll notice a CPF column, that's the social number, that's why I hid it, I also amended your code to filter by the CPF number. But even when the original table has only 1 CPF it didn't work.

 

Here is the amended code

AugmentedTable = 
ADDCOLUMNS ( 
    ADDCOLUMNS ( 
        ADDCOLUMNS ( 'Cálculo', 
                    "Draft", 
                    VAR StartDate = [Date]
                    VAR EndDate = StartDate + 7
                    VAR CPFSelected = [CPF]
                    RETURN SUMX ( FILTER ( 'Cálculo', 'Cálculo'[Date] >= StartDate && 'Cálculo'[Date] <= EndDate && [CPF] = CPFSelected), [Tickets] ) - 1 ),
        "Flag",
        VAR CurrentDate = 'Cálculo'[Date]
        VAR PreviousDate = CurrentDate - 7
        VAR CurrentCPF = 'Cálculo'[CPF]
        VAR ExistingPreviousDate = MINX ( FILTER ( 'Cálculo', 'Cálculo'[Date] < CurrentDate && 'Cálculo'[Date] >= PreviousDate && 'Cálculo'[CPF]=CurrentCPF), 'Cálculo'[Date] )
        RETURN IF ( MINX ( FILTER ( 'Cálculo', 'Cálculo'[Date] = ExistingPreviousDate ), [Draft] ) > 0, 1, 0 ) ),
    "Result",
    IF ( [Flag] = 0, [Draft], 0 ) )

 

  • barritown's avatar
    barritown
    Solution Sage
    3 years ago

    Hi rodrigo_avf,

    I see. My approach apparently doesn't fit. I tried a couple of other ideas today, but they didn't work either. Should I solve it via some programming language, I'd get rid of the odd dates in a series of cycles first and then perform the calculation for the rest, but that's not about [DAX].

    Hope someone from Community solves your problem, I'll be happy to watch and learn.

    Should I get an "Evrika!" insight, I'll get back to you.

    Best Regards,

    Alexander

    My YouTube vlog in English

    My YouTube vlog in Russian

    • rodrigo_avf's avatar
      rodrigo_avf
      Frequent Visitor
      3 years ago

      Thanks for trying barritown 
      The workaround I did was to get the range of dates each date falls in using Excel, wich is super easy to do, but as my data has more than 300k rows and growing, I had to split it into different Excel files because the formula is a bit heavy.
      After that I import everything to Power BI and finish the calculations there.

      But still would be really nice to learn how to solve this using DAX and/or M only, it would also make things simpler.