Matrix with Weekly Data Columns and String Values

[Anonymous]

I'm new to PowerBI and I'm trying to create the visualization below and am having trouble because of a few areas:

 

1. I am getting an error with dates because there are gaps in the dates I'm using due to them being weekly data.

2. I am having trouble re-formatting dates because PowerBI is putting in a long-winded full date ("Monday, January 1, 2018" instead of "01/01/2018")

3. I have not figured out how to do a countif using strings.

Any help or advice would be greatly appreciated.

Thanks!

[MFelix · Accepted solution]

Hi  @Anonymous,

I'm assuming that you want to return Yes when measure is greater than target.

Use the following measure to add to your matrix:

YES / NO = 
IF (
    HASONEFILTER ( Data[Week Starting] );
    IF ( SUM ( Data[Measure] ) < SUM ( Data[Target] ); "No"; "Yes" );
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                Data;
                Data[Week Starting];
                "YES/NO"; SUM ( Data[Measure] ) - SUM ( Data[Target] )
            );
            [YES/NO] > 0
        )
    )
)

Regards,

MFelix

[MFelix · Accepted solution]

Hi  @Anonymous,

I'm assuming that you want to return Yes when measure is greater than target.

Use the following measure to add to your matrix:

YES / NO = 
IF (
    HASONEFILTER ( Data[Week Starting] );
    IF ( SUM ( Data[Measure] ) < SUM ( Data[Target] ); "No"; "Yes" );
    COUNTROWS (
        FILTER (
            SUMMARIZE (
                Data;
                Data[Week Starting];
                "YES/NO"; SUM ( Data[Measure] ) - SUM ( Data[Target] )
            );
            [YES/NO] > 0
        )
    )
)

Regards,

MFelix

[Anonymous]

Thanks Felix!! This is great! Any advice on how to adjust the format of the dates as columns?

Cheers,

Julian

[MFelix · Accepted solution]

Hi @Anonymous,

Select the date column then select the modeling on the bar and format the date as you need.

Regards,

MFelix