Sync two columns for visualization

[EshaanG]

Hello,

I have a data like this

PersonID  TransactionID  TransactionMonth  Training   TrainingMonth

1               X1                    January 2020          C1            February 2020

1               X2                    February 2020        C2            February 2020

2               X3                    February 2020        C2            February 2020

I want to summarize it like below :

Month                 # Transactions       # Trainings

January 2020       1                             0

February 2020     2                             3

How do I sync the two months onto a common month so I can show both metrics?

Many Thanks!

[v-lionel-msft]

Hi @EshaanG ,

//measure
Transactions = 
CALCULATE(
    COUNT(Sheet4[  TrainingMonth]),
    ALLEXCEPT(
        Sheet4,
        Sheet4[TransactionMonth ]
    )
)

//measure
Trainings = 
VAR x = 
CALCULATE(
    COUNT(Sheet4[  TrainingMonth]),
    FILTER(
        ALL(Sheet4),
        Sheet4[  TrainingMonth] = MAX(Sheet4[TransactionMonth ])
    )
)
RETURN
IF(
    x = BLANK(),
    0,
    x
)

Best regards,
Lionel Chen

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

[HashamNiaz · Accepted solution]

Hi !

Try creating anther inactive relatioship between your date & training month column.

Then write DAX by utilizing this inactive relationship. You can watch following video on how to implement this in your scenario;

https://www.youtube.com/watch?v=ppxcpuNMBoE&feature=youtu.be&t=86 

Regards,