Data In Rows with Multiple Dates in Columns

[jsetzer81]

Hello,

 I am currently building a few reports and want the main summary report to have all of our KPI Daily, Week to Date and Period to date as shown in the "goal" picture attached. Currently - I have the data for a single day - today.png -  showing but cant figure out how to show daily, WTD and PTD next to each other in columns. 

[v-jiascu-msft]

Hi @jsetzer81,

Can you share a sample of the original data model? A dummy pbix file would be great. Please also share the way to calculate KPI.

Best Regards,

Dale

[jsetzer81]

Dale - here is the link to the file

The calculations for the KPI are already done but looking to be able to have daily, week to date and period to date next to each other versus different pages. We currently run 13 Periods each consisting of 4 weeks (a 5th week once every 5 years or so) and our weeks run Tuesday - Monday. Please let me know what other information you may need

[v-jiascu-msft]

Hi @jsetzer81,

It seems you can create the visual. 

1. What does it mean? The "versus different pages".

2. The measures respond to the context. So if we have dates, the WTD could be the same for one week. 

3. Did you try to add the date context? The snapshot below is what I tried.

BTW, your file link is available to the whole Internet. Please protect your privacy.

Best Regards,

Dale

[jsetzer81]

Dale,

 Can you send me the PBX file for what you tried? Thanks!

[v-jiascu-msft]

Hi @jsetzer81,

I worked again with your new information. Please refer to the measures below. And also check your private Message.

Daily = sum(sus[cb_Net_Sales_Amt])

Weekly =
VAR selectedWeek =
    IF (
        HASONEVALUE ( 'DateKey 2'[Date] ),
        MIN ( 'DateKey 2'[PeriodWeek] ),
        BLANK ()
    )
RETURN
    IF (
        ISBLANK ( selectedWeek ),
        BLANK (),
        CALCULATE (
            SUM ( sus[cb_Net_Sales_Amt] ),
            FILTER (
                ALL ( 'DateKey 2' ),
                'DateKey 2'[PeriodWeek] = selectedWeek
                    && 'DateKey 2'[Date] <= MIN ( 'DateKey 2'[Date] )
            )
        )
    )

PTD =
VAR selectedPeriod =
    IF ( HASONEVALUE ( 'DateKey 2'[Date] ), MIN ( 'DateKey 2'[Period] ), BLANK () )
RETURN
    IF (
        ISBLANK ( selectedPeriod ),
        BLANK (),
        CALCULATE (
            SUM ( sus[cb_Net_Sales_Amt] ),
            FILTER (
                ALL ( 'DateKey 2' ),
                'DateKey 2'[Period] = selectedPeriod
                    && 'DateKey 2'[Date] <= MIN ( 'DateKey 2'[Date] )
            )
        )
    )

Best Regards,

Dale

[jsetzer81]

Dale,

 Thanks for sending and putting your time into it! Is there any reason that all the metrics have the same value and the metrics (values) arent individually seperated like the DBS tab? Also, I am not sure if its allowd to discuss on the forum but do you freelance and if so what is your rate? Again thanks for the support!

[v-jiascu-msft]

Hi @jsetzer81,

There is only one "restaurant_id" in the sample data. Did you test the formula in your dev dataset? As we can see from the snapshot, they are all the same except the dates. Aren't they duplicates?

Best Regards,

Dale

[jsetzer81]

Dale,

 Can you replicate what you did on Page 2 (Daily, WTD, PTD) on the DBS tab not making any changes but adding those columns? That would solve my entire issue. Again, thanks for the support

[v-jiascu-msft]

Hi @jsetzer81,

I'm afraid we can't do that. In the tab DBS, you put all the measures in the values and leave the Rows empty. If we put the measures in the values, it just gets more rows. What's the expected result? Is it in your first post?

Best Regards,

Dale

[jsetzer81]

Hey Dale it looks like the data you are showing is doing some type of count.  The versus diferent pages just meant I wanted Daily, Week To Date and PEriod to Date on same page/tab. I'm looking for the page to show the  business date the person selects, the week to date for then - so if they select 05/31/18 - It was show 05/31/18 as daily, 5/29 - 5/31 for week to date (weeks run Tuesday - Monday), and 05/15 - 05/31 for Period to date. Does that make sense?