Create a table with different values

[Pure_123]

Hi Guys,

I am new to power BI and looking to create a table like below power BI.

and my data set looks like below (It's a sql view)

I have called 3 parameters in my sql

DECLARE @YesterdaysDate DATE = dateadd(d, (datediff(d, 0, GETDATE() -1)) , 0)

DECLARE @Last7Days DATE = dateadd(d, (datediff(d, 0, GETDATE() -7)) , 0)

DECLARE @FirstofthisMonth DATE = DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0)

and to get this value

sql code is :

SELECT

                 Count(ApplicationDate)

WHERE

                  ApplicationDate = @YesterdaysDate

I have tried with measures and it didn't work for me.

Can someone please help me to create this in Power Bi please.

Thanks in Adva

[Icey · Accepted solution]

Hi @Pure_123 ,

I doesn't use your sql parameter and doesn't change your table structure.

Please check:

1. Enter data to create two tables.

Period table:

Category table:

2. Sort [Period] column by [Order] column, [Category] column with [Order] column.

3. No relationships among the three tables.

4. Create a measure.

Measure = 
VAR Yesterday_ =
    TODAY () - 1
VAR Last_7_days =
    TODAY () - 7
VAR So_far_this_month_start =
    DATE ( YEAR ( TODAY () ), MONTH ( TODAY () ), 1 )
VAR So_far_this_month_end =
    EOMONTH ( TODAY (), 0 )
VAR Applications =
    SWITCH (
        SELECTEDVALUE ( 'Period'[Period] ),
        "Yesterday", CALCULATE (
            COUNT ( 'Table'[ApplicationDate] ),
            'Table'[ApplicationDate] = Yesterday_
        ),
        "Last 7 days", CALCULATE (
            COUNT ( 'Table'[ApplicationDate] ),
            FILTER (
                'Table',
                'Table'[ApplicationDate] >= Last_7_days
                    && 'Table'[ApplicationDate] <= TODAY()
            )
        ),
        "So far this month", CALCULATE (
            COUNT ( 'Table'[ApplicationDate] ),
            FILTER (
                'Table',
                'Table'[ApplicationDate] >= So_far_this_month_start
                    && 'Table'[ApplicationDate] <= So_far_this_month_end
            )
        )
    ) + 0
VAR Offers =
    SWITCH (
        SELECTEDVALUE ( 'Period'[Period] ),
        "Yesterday", CALCULATE ( COUNT ( 'Table'[OfferDate] ), 'Table'[OfferDate] = Yesterday_ ),
        "Last 7 days", CALCULATE (
            COUNT ( 'Table'[OfferDate] ),
            FILTER (
                'Table',
                'Table'[OfferDate] >= Last_7_days
                    && 'Table'[OfferDate] <= TODAY()
            )
        ),
        "So far this month", CALCULATE (
            COUNT ( 'Table'[OfferDate] ),
            FILTER (
                'Table',
                'Table'[OfferDate] >= So_far_this_month_start
                    && 'Table'[OfferDate] <= So_far_this_month_end
            )
        )
    ) + 0
VAR Completions =
    SWITCH (
        SELECTEDVALUE ( 'Period'[Period] ),
        "Yesterday", CALCULATE (
            COUNT ( 'Table'[CompleteDate] ),
            'Table'[CompleteDate] = Yesterday_
        ),
        "Last 7 days", CALCULATE (
            COUNT ( 'Table'[CompleteDate] ),
            FILTER (
                'Table',
                'Table'[CompleteDate] >= Last_7_days
                    && 'Table'[CompleteDate] <= TODAY()
            )
        ),
        "So far this month", CALCULATE (
            COUNT ( 'Table'[CompleteDate] ),
            FILTER (
                'Table',
                'Table'[ApplicationDate] >= So_far_this_month_start
                    && 'Table'[CompleteDate] <= So_far_this_month_end
            )
        )
    ) + 0
RETURN
    SWITCH (
        SELECTEDVALUE ( Category[Category] ),
        "Applications", Applications,
        "Offers", Offers,
        "Completions", Completions
    )

5. Create a Matrix visual.

BTW, .pbix file attached.

Best Regards,

Icey

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

[Pure_123]

Looks like the data set is missing from the post

[Pure_123]

@Icey This is spot on. Thank you so very much 😇

[Pure_123]

@Icey And very clear guidance too..