Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

DAX

I need the following in DAX format.   Requirement: I have a table called Transactions which has 3 columns ID, [Billing Type] and [Year-Month] as mentioned below. I need to create a measure that sho...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    Anonymous

     

    Please change the MEASURE to following

     

    But i Have following observtions

    1) 10 th occurence of ID 2 is in June

    2) November 2017 date doesn't exist in your data

     

     

    Measure =
VAR mycount =
    CALCULATE (
        COUNT ( TableName[ID] ),
        FILTER (
            ALL ( TableName ),
            TableName[Year-Month] <= SELECTEDVALUE ( TableName[Year-Month] )
                && TableName[RANK] = 10
        )
    )
RETURN
    IF ( ISBLANK ( mycount ), 0, mycount )
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

Hi Anonymous

 

Try this technique

 

1) First Add an Index Column from the Query Editor

 

2) Then Add a RANK Calculated Colum

RANK =
RANKX (
    FILTER ( TableName, TableName[ID] = EARLIER ( TableName[ID] ) ),
    TableName[Index],
    ,
    ASC,
    DENSE
)

3) Now you can use this MEASURE

 

Measure =
CALCULATE ( COUNT ( TableName[ID] ), TableName[RANK] = 10 )

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Sorry Zubair_Muhammad, The result is not as expected 

     

    Until April (2017-05) there is no 10th Credit Card. We have 1 in May (2017-05) and then 2 in October (2017-10). So we have to plot 0 till April and 1 till September and since we have 2 in October, we have to sum up the May 1 and October 2 and show as 3 from October to December

     

    • Zubair_Muhammad's avatar
      Zubair_Muhammad
      Community Champion
      8 years ago

      Anonymous

       

      Please change the MEASURE to following

       

      But i Have following observtions

      1) 10 th occurence of ID 2 is in June

      2) November 2017 date doesn't exist in your data

       

       

      Measure =
VAR mycount =
    CALCULATE (
        COUNT ( TableName[ID] ),
        FILTER (
            ALL ( TableName ),
            TableName[Year-Month] <= SELECTEDVALUE ( TableName[Year-Month] )
                && TableName[RANK] = 10
        )
    )
RETURN
    IF ( ISBLANK ( mycount ), 0, mycount )
      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Thanks Zubair_Muhammad

         

        This is exactly what I'm looking for. Thanks again!