Sales by week and then one column for YTD Actuals and YTD Budget

[JJ2022PBI]

Hi,

I am struggling with what is probably quite a basic requirement in PowerBI.

I am trying to build a table that has weekly sales and then YTD actuals and YTD budget on the right hand side. I have the YTD actual and YTD measures already but I am struggling to make this set up work in a table because I don't want to see the budget by week, just once for YTD budget column. I have attached the below in excel which is what I am trying to achieve.

I have a seperate date table so I can drill down from quarters to months and then weeks for the actual data.

Thanks for your help!

[MarkLaf]

There are hacky ways to do this. One approach would be to add a table to your model with rows for the custom columns you want, then build a measure to do different calculations depending on the column.

Dummy data used

Fact:

Item	Date	Value
Item 1	1/1/2022	12
Item 1	1/5/2022	43
Item 1	1/16/2022	32
Item 1	1/23/2022	21
Item 1	1/31/2022	34
Item 2	2/5/2022	11
Item 2	2/10/2022	54
Item 2	2/20/2022	21

Dates:

Dates = 
VAR mindt = DATE( YEAR( MIN( 'Fact'[Date] ) ), 1, 1 )
VAR maxdt = DATE( YEAR( MAX( 'Fact'[Date] ) ), 12, 31 )
VAR basedts = CALENDAR( mindt, maxdt )
VAR basecal = 
GENERATE(
    basedts,
    VAR _weekno = WEEKNUM( [Date] )
    RETURN
    ROW(
        "Week No", _weekno,
        "Week", "Week " & _weekno
    )
)
RETURN
basecal

Relationship:

Steps

Create your table with custom axis/columns:

Week+YTD Hack Table = 
VAR _wks =
    ADDCOLUMNS (
        DISTINCT ( Dates[Week No] ),
        "Label", "Week " & [Week No],
        "Type", "wk"
    )
VAR _custom =
    DATATABLE (
        "Week No", INTEGER,
        "Label", STRING,
        "Type", STRING,
        {
            { 100, "YTD Sales", "ytds" },
            { 101, "YTD Budget", "ytdb" },
            { 102, "YTD Variance", "ytdv" }
        }
    )
RETURN
    UNION ( _wks, _custom )

'Week+YTD Hack Table' Output:

Once generated, you'll want to:

1) set an *inactive* relationship between 'Week+YTD Hack Table'[Week No] and Dates[Week No]

2) set the Sort by column of Label to Week No. Label is what we'll put in the visual, so ensure its sort order makes sense by setting Week No appropriately. 

Now put together the measure using SWITCH to perform a specific calculation based on 'Week+YTD Hack Table'[Type]. Note that we'll need to use USERELATIONSHIP whenever we want to leverage date table relationships (e.g. to Fact in this scenario)

Week+YTD Hack =
VAR _sum =
    CALCULATE (
        SUM ( 'Fact'[Value] ),
        USERELATIONSHIP ( 'Week+YTD Hack Table'[Week No], Dates[Week No] )
    )
VAR _ytd =
    CALCULATETABLE ( DATESYTD ( Dates[Date] ), Dates[Date] = MAX ( Dates[Date] ) )
VAR _ytds =
    CALCULATE ( SUM ( 'Fact'[Value] ), _ytd )
VAR _ytdb =
    //dummy numbers for budget as I didn't bother adding to test data
    CALCULATE (
        SUMX ( CROSSJOIN ( VALUES ( 'Fact'[Item] ), VALUES ( Dates[Week No] ) ), 1 ),
        _ytd
    )
VAR _ytdv = _ytds - _ytdb
RETURN
    SWITCH (
        SELECTEDVALUE ( 'Week+YTD Hack Table'[Type] ),
        "wk", _sum,
        "ytds", _ytds,
        "ytdb", _ytdb,
        "ytdv", _ytdv
    )

Now create a matrix visual, add Fact[Item] to rows, add 'Week+YTD Hack Table'[Label] to columns, add [Week+YTD Hack] to values, and turn off row subtotals in formatting:

[JJ2022PBI]

Thanks very much for your help @MarkLaf .

I have created the new table using the summarizecolumns function (screenshot below), the result looks the same as what you typed above. In the new table 'Capabilities' refers to item int he previous exame and 'Flow' refers to sales. When I used the dates table and updated the name of the fact table I get this error message "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value". When I googled this it seems like it may be due to the new table having too many columns?

[MarkLaf]

It's hard to provide good guidance without a more complete understanding of your full model.

It looks like you put the Dates code into a measure but that was simply the DAX to build up a dates table for my dummy data (it was under 'dummy data used' heading - sorry if not clear). Use your own Dates table and be sure to add calculated columns [Week No] and [Week] if you don't already have equivalents.

You don't need an intermediate aggregation table, so you should be able to scrap 'Weekly Wholesale Flows', the [Week+YTD Hack] measure will handle aggregations.

So, you should already have a Dates table related to your fact (WSMapping?). If you already have that set up, then all you need to do is build the calculated table 'Week+YTD Hack Table', add an inactive relationship 'Week+YTD Hack Table' --1:M--> 'Dates' on the [Week No] column. The [Week+YTD Hack] measure should then work when you include 'Week+YTD Hack Table' columns in the visual.

[JJ2022PBI]

Thanks very much @MarkLaf . That table now works.

Do you know how I would add a date hierarchy for the weeks? This would allow me to group up weeks 1-12 into Q1 and then into Jan, Feb, March.

Thanks again for your help.

[lbendlin]

weeks are incompatible with months and quarters, especially if you look at calendar weeks.  You will need to define how to handle weeks that extend across month or quarter boundaries.

[JJ2022PBI]

Hi @lbendlin . I have a date table that is quite expansive so each each day is assigned a week number, month, quarter, year, etc. (screenshot below).

I created a quick table which shows that when a week straddles two months it is shown twice, in the below screenshot you can see week 5 is in January as well as Feb.

I thought I would have just alter the table that @MarkLaf shared to add more granularity to include days, weeks months, quarters and years and but still summarise by week.

[MarkLaf · Accepted solution]

What I provided before was very specifically for the week breakdown scenario from your OP. If you want a more flexible setup, contstruct the hack table so there is a row for each date and set up the relationship on the [Date] column between hack table and Dates. This way, you will indeed see weeks repeat across months/quarters, but your Fact data will be related correctly to the right month/quarter.

You can use a similar approach as before and just reproduce all your Dates columns in the hack table that you may want in your visual.

Here is some updated code you can try - I tried it with an expanded dummy data table, but won't share as overall structure is same as my first response. Note that I ended up switching cardinality between Dates and the hack table, just requires a small tweak to DAX and keeps you in star/constellation schema.

New hack table:

Dates+YTD Hack Table =
VAR _maxYr =
    MAX( Dates[Year] )
VAR _maxQr =
    MAX( Dates[Quarter No] )
VAR _maxMo =
    MAX( Dates[Month No] )
VAR _maxWk =
    MAX( Dates[Week No] )
VAR _customInputs =
    DATATABLE(
        "Label", STRING,
        "Order", INTEGER,
        {
            { "YTD Sales", 1 },
            { "YTD Budget", 2 },
            { "YTD Variance", 3 }
        }
    )
VAR _baseDts =
    SELECTCOLUMNS(
        Dates,
        "Date", [Date],
        "Year No", [Year],
        "Quarter No", [Quarter No],
        "Month No", [Month No],
        "Week No", [Week No],
        "Year", [Year],
        "Quarter", [Quarter],
        "Month", [Month],
        "Week", [Week],
        "Type", "dt"
    )
VAR _customRows =
    SELECTCOLUMNS(
        _customInputs,
        "Date", BLANK(),
        "Year No", _maxYr + [Order],
        "Quarter No", _maxQr + [Order],
        "Month No", _maxMo + [Order],
        "Week No", _maxWk + [Order],
        "Year", [Label],
        "Quarter", [Label],
        "Month", [Label],
        "Week", [Label],
        "Type", [Label]
    )
VAR final =
    UNION( _baseDts, _customRows )
RETURN
    final

Note: make sure to set Sort by column for the Year/Quarter/Month/Weeks to the corresponding 'No' version of column (e.g. [Year No])

Model diagram:

New hack measure:

Dates+YTD Hack =
VAR _dtsLabel = "dt"
VAR _ytdsLabel = "YTD Sales"
VAR _ytdbLabel = "YTD Budget"
VAR _ytdvLabel = "YTD Variance"
VAR _sum =
    CALCULATE(
        SUM( 'Fact'[Value] ),
        FILTER(
            Dates,
            CALCULATE(
                NOT ISEMPTY( 'Dates+YTD Hack Table' ),
                USERELATIONSHIP ( 'Dates+YTD Hack Table'[Date], Dates[Date] )
            )
        )
    )
VAR _ytd =
    CALCULATETABLE( DATESYTD( Dates[Date] ), Dates[Date] = TODAY() )
VAR _ytds =
    CALCULATE( SUM( 'Fact'[Value] ), _ytd )
VAR _ytdb =
    //dummy numbers for budget as I didn't bother adding to test data
    CALCULATE(
        SUMX( CROSSJOIN( VALUES( 'Fact'[Item] ), VALUES( Dates[Week No] ) ), 1 ),
        _ytd
    )
VAR _ytdv = _ytds - _ytdb
RETURN
    SWITCH(
        SELECTEDVALUE( 'Dates+YTD Hack Table'[Type] ),
        _dtsLabel, _sum,
        _ytdsLabel, _ytds,
        _ytdbLabel, _ytdb,
        _ytdvLabel, _ytdv
    )

You can then set up something like the below matrix

You can tweak the _customRows VAR of the table so only one level of the matrix displays the custom label. E.g. below change to VAR...

VAR _customRows =
    SELECTCOLUMNS(
        _customInputs,
        "Date", BLANK(),
        "Year No", _maxYr + [Order],
        "Quarter No", _maxQr + [Order],
        "Month No", _maxMo + [Order],
        "Week No", _maxWk + [Order],
        "Year", BLANK(), //changed to blank
        "Quarter", BLANK(), //changed to blank
        "Month", [Label],
        "Week", BLANK(), //changed to blank
        "Type", [Label]
    )

... means you'll get:

Of course, not very flexible: if you wanted this somewhere and a different visual that didn't have Months in it, then it wouldn't meet your requirements.

Another approach

I also came up with another approach that lets you choose just 1-2 top levels in your date hierarchy (e.g. year and quarter) to include in hack table, and then you can use any lower levels from hierarchy from Dates. It's a lot more flexible and I like it more than the above. It does, however, require adding subtotals to the matrix, which doesn't match your OP visual as well.

Here is DAX for the hack table. It's pretty similar except: 1) I'm only including Years and Quarters in this table with the expectation that I'll always have one or both in a visual and then everything else lower in hierarchy can come from Dates; 2) because we are just dealing with two fields, I added two versions for each, one with the label and one that is blank, which will let us use one or the other to avoid double column headers; 3) I've used zero-width non-joiner for the blanks in order to still apply sort with 'No' columns.

DAX for the hack table:

Dates+YTD Hack Totals Table = 
VAR _maxYr =
    MAX( Dates[Year] )
VAR _maxQr =
    MAX( Dates[Quarter No] )
VAR _maxMo =
    MAX( Dates[Month No] )
VAR _maxWk =
    MAX( Dates[Week No] )
VAR _customInputs =
    DATATABLE(
        "Label", STRING,
        "Order", INTEGER,
        {
            { "YTD Sales", 1 },
            { "YTD Budget", 2 },
            { "YTD Variance", 3 }
        }
    )
VAR _baseDts =
    SELECTCOLUMNS(
        Dates,
        "Date", [Date],
        "Year No", [Year],
        "Quarter No", [Quarter No],
        "Year_Hack", [Year],
        "Quarter_Hack", [Quarter],
        "Year_Blank", [Year],
        "Quarter_Blank", [Quarter],
        "Type", "dt"
    )
VAR _customRows =
    SELECTCOLUMNS(
        _customInputs,
        "Date", BLANK(),
        "Year No", _maxYr + [Order],
        "Quarter No", _maxQr + [Order],
        "Year_Hack", [Label],
        "Quarter_Hack", [Label],
        "Year_Blank", REPT(UNICHAR(8204),[Order]),
        "Quarter_Blank", REPT(UNICHAR(8204),[Order]),
        "Type", [Label]
    )
VAR final =
    UNION( _baseDts, _customRows )
RETURN
    final

Be sure to set the Order by column to the 'No' columns for *both* the Hack and Blank versions of columns.

(relationship is same as above, so will skip the relationship diagram)

Hack measure to use - note that it's basically the same except we're using NOT ISFILTERED( Dates ) to skip the measure being evaluated/displaying with any fields from Dates; this is what lets us add lower hierarchy levels from Dates:  

Dates+YTD Hack Totals = 
VAR _dtsLabel = "dt"
VAR _ytdsLabel = "YTD Sales"
VAR _ytdbLabel = "YTD Budget"
VAR _ytdvLabel = "YTD Variance"
VAR _sum =
    CALCULATE (
        SUM ( 'Fact'[Value] ),
        CALCULATETABLE( Dates, CALCULATETABLE( 'Dates+YTD Hack Totals Table', USERELATIONSHIP ( 'Dates+YTD Hack Totals Table'[Date], Dates[Date] ) ) )
    )
VAR _ytd =
    CALCULATETABLE ( DATESYTD ( Dates[Date] ), Dates[Date] = TODAY() )
VAR _ytds =
    CALCULATE ( SUM ( 'Fact'[Value] ), _ytd )
VAR _ytdb =
    //dummy numbers for budget as I didn't bother adding to test data
    CALCULATE (
        SUMX ( CROSSJOIN ( VALUES ( 'Fact'[Item] ), VALUES ( Dates[Week No] ) ), 1 ),
        _ytd
    )
VAR _ytdv = _ytds - _ytdb


RETURN
    SWITCH (
        SELECTEDVALUE ( 'Dates+YTD Hack Totals Table'[Type] ),
        _dtsLabel, _sum,
        _ytdsLabel, IF( NOT ISFILTERED( Dates ), _ytds ),
        _ytdbLabel, IF( NOT ISFILTERED( Dates ), _ytdb ),
        _ytdvLabel, IF( NOT ISFILTERED( Dates ), _ytdv ),
        0
    )

With the above, you can get a visual like what I shared above - just pasting again for reference:

In this, I'm using [Year_Hack] (has the calc labels) and [Quarter_Blank] (zero-width characters) from the hack table, but Months and Weeks are from Dates.

To get it to look like this, you have to fiddle with the subtotal displays. Here I turned off subtotals for all the columns except Months.

As I mentioned above, I think I like this approach a little better since you can use any fields lower-level fields from Dates

[JJ2022PBI]

Great, thank you @MarkLaf !

[lbendlin]

only if you show years and weeks. Not if you also show quarters or months.

(unless you are ok with listing the same week twice - not sure what the business value of that is though)

[lbendlin]

This is not how Power BI works.  Use separate table visuals.  Putting them side by side will run into screen space issues.

(You can create custom matrix visuals, but doing that with variable number of columns is not possible. )