Conditional Record Counts with YTD and PYTD Measures

kkieger · April 13, 2020, 5:55pm

Hello,

I’ve attached a rather modest PBIX file of a sales (orders) fact table, a calendar table, and some key measures. I have the YTD, PYTD, and Previous Year sales measures working as expected. I am using a column as “IsPast” column (flag) in my Calendar table - based on the max( fact able date ) - to support the Previous YTD calcs.

A simple “Increase_Decrease” measure also exists to compare current year and previous year sales.

While the sales measures are useful, another key requirement is to produce the counts (of distinct Product IDs) which reflect an Increase or Decrease (or No Change) between current and previous YTD. It is here where capturing the proper counts in a measure in not obvious to me; mainly due to variations in count requirements. Here’s the simple record count rules per unique Product ID:
YTD Sales - PYTD Sales > 0, then increment “Increase” count ;
YTD Sales - PYTD Sales < 0, then increment “Decrease” count ;
YTD Sales - PYTD Sales = 0, then increment “No Change” count

Here’s the conditions which impact the record counts:

When a [Order] record exists with $0. sales in either the YTD or PYTD time frames, it is always counted (note: ‘Increase_Decrease’ measure = $0. in these cases)
When no [Order] records exist in either YTD and PYTD time frames - but do exist in the Previous Year - then I need a ‘Count’ measure which increments the ‘No Change’ count, and a 'Count measure which does not increment ‘No Change’ (note: ‘Increase_Decrease’ measure is blank and ‘Sales PY’ value is not blank in these cases).

I’m just looking for your insights to produce counts of unique product IDs which ‘Increase’, which ‘Decrease’, and which have ‘No Change’ (under the two definition variations described) in YTD and PYTD Sales.

Thank you,
KevinSample Dataset for PYTD.pbix (199.8 KB)

Melissa · April 13, 2020, 7:09pm

Hi @kkieger,

I must say your requirement isn’t completely clear to me (yet) but I’ve added a few measures that should get us started. Can you look them over and perhaps show in an Excel table what the desired result should look like, based on what you have visualized?

I’m confident we can solve this. Thanks!

Sample Dataset for PYTD.pbix (204.5 KB)

kkieger · April 13, 2020, 7:50pm

Thanks, Melissa. Definite progress since your Count values of ‘No Change’, ‘Increase’, and ‘Decrease’ are accurate.

In the context of the measures and filtered visualizations in your/our PBIX, I am looking to display a total count of distinct Product IDs = 7 where ‘Increase’ count = 3, ‘Decrease’ count = 2, and ‘No Change’ count = 2. Producing those three counts and their sum (total) in either a table or - preferably - as individual measures in Card visualizations is the main objective.

The second objective is a variation to include the four other Product IDs displayed… total count of distinct Product IDs = 11 where ‘Increase’ count = 3, ‘Decrease’ count = 2, and ‘No Change’ count = 6 (due to the blanks in YTD and PYTD measures, but with PY Sales $).

I hope that makes sense. I have added a bit more supporting info in the attached.

Kind regards,
Kevin
Enterprise DNA - Supporting YTD and PYTD Info.docx (38.8 KB)

EnterpriseDNA · April 13, 2020, 10:27pm

Thanks for posting your question @kkieger. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question. Thanks!

Melissa · April 14, 2020, 7:03pm

So big thanks for mentioning this as a requirement! Now this is what I’ve got for you:
For the Count Type a simple SWITCH, TRUE statement will do:

Count Type = 
    SWITCH( TRUE(),
        ISBLANK([Sales YTD]) && ISBLANK([Sales PYTD]), BLANK(),
        [Increase_Decrease] >0, "Increase",
        [Increase_Decrease] <0, "Decrease",
        [Increase_Decrease] =0, "No Change"
    )

.
Also relatively straight forward the Increase count:

Count Increase = 
COUNTROWS(
    FILTER(
        SUMMARIZE( Orders, Orders[Product ID], People[Sales Rep] ),
        [Increase_Decrease] >0 
    )
)

.
This is where it gets tricky because for the Decrease count we have to account for the PYTD values:

Count Decrease = 
VAR ThisYear =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                ADDCOLUMNS(
                    SUMMARIZE( Orders, Orders[Product ID], People[Sales Rep] ),
                    "__YTD Sales", CALCULATE( [Total Sales], DATESYTD('calendar'[Date] )),
                    "__PYTD Sales", CALCULATE( [Total Sales], SAMEPERIODLASTYEAR ( 'calendar'[Date] ), 'calendar'[IsPast] = TRUE() )
                ),
                ([__YTD Sales] - [__PYTD Sales]) <0
            ), "ProdID", [Product ID]
        )
    )
VAR LastYear =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                ADDCOLUMNS(
                    SUMMARIZE( CALCULATETABLE( Orders, SAMEPERIODLASTYEAR( 'calendar'[Date] )), Orders[Product ID], People[Sales Rep] ),
                    "__YTD Sales", CALCULATE( [Total Sales], DATESYTD('calendar'[Date] )),
                    "__PYTD Sales", CALCULATE( [Total Sales], SAMEPERIODLASTYEAR ( 'calendar'[Date] ), 'calendar'[IsPast] = TRUE() )
                ),
                ([__YTD Sales] - [__PYTD Sales]) <0
            ), "ProdID", [Product ID]
        )
    )
VAR Result = DISTINCT( UNION( ThisYear, LastYear ))
RETURN

    COUNTROWS( Result )

.
And the same is true for both No Change calculations here we also have to account for the PYTD

Count No Change = 
VAR ThisYear =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                ADDCOLUMNS(
                    SUMMARIZE( Orders, Orders[Product ID], People[Sales Rep] ),
                    "__YTD Sales", CALCULATE( [Total Sales], DATESYTD('calendar'[Date] )),
                    "__PYTD Sales", CALCULATE( [Total Sales], SAMEPERIODLASTYEAR ( 'calendar'[Date] ), 'calendar'[IsPast] = TRUE() )
                ),
                ([__YTD Sales] - [__PYTD Sales]) =0
            ), "ProdID", [Product ID]
        )
    )
VAR LastYear =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                ADDCOLUMNS(
                    SUMMARIZE( CALCULATETABLE( Orders, SAMEPERIODLASTYEAR( 'calendar'[Date] ), 'calendar'[IsPast] = TRUE ), Orders[Product ID], People[Sales Rep] ),
                    "__YTD Sales", CALCULATE( [Total Sales], DATESYTD('calendar'[Date] )),
                    "__PYTD Sales", CALCULATE( [Total Sales], SAMEPERIODLASTYEAR ( 'calendar'[Date] ), 'calendar'[IsPast] = TRUE() )
                ),
                ([__YTD Sales] - [__PYTD Sales]) =0
            ), "ProdID", [Product ID]
        )
    )
VAR Result = DISTINCT( UNION( ThisYear, LastYear ))
RETURN

    COUNTROWS( Result )

.

Count No Change v2 = 
VAR ThisYear =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                ADDCOLUMNS(
                    SUMMARIZE( Orders, Orders[Product ID], People[Sales Rep] ),
                    "__YTD Sales", CALCULATE( [Total Sales], DATESYTD('calendar'[Date] )),
                    "__PYTD Sales", CALCULATE( [Total Sales], SAMEPERIODLASTYEAR ( 'calendar'[Date] ), 'calendar'[IsPast] = TRUE() )
                ),
                ([__YTD Sales] - [__PYTD Sales]) =0
            ), "ProdID", [Product ID]
        )
    )
VAR LastYear =
    DISTINCT(
        SELECTCOLUMNS(
            FILTER(
                ADDCOLUMNS(
                    SUMMARIZE( CALCULATETABLE( Orders, SAMEPERIODLASTYEAR( 'calendar'[Date] )), Orders[Product ID], People[Sales Rep] ),
                    "__YTD Sales", CALCULATE( [Total Sales], DATESYTD('calendar'[Date] )),
                    "__PYTD Sales", CALCULATE( [Total Sales], SAMEPERIODLASTYEAR ( 'calendar'[Date] ), 'calendar'[IsPast] = TRUE() )
                ),
                ([__YTD Sales] - [__PYTD Sales]) =0
            ), "ProdID", [Product ID]
        )
    )
VAR Result = DISTINCT( UNION( ThisYear, LastYear ))
RETURN

    COUNTROWS( Result )

.
Now if someone has a better way to calculate these values, I would certainly like to know about it - so then please respond to this post. Thanks!

This is what it looks like on the report.

Thanks Kevin - I certainly learned from this post
Sample Dataset for PYTD.pbix (217.9 KB)

kkieger · April 14, 2020, 8:27pm

Amazing job, Melissa. 100% comprehension of my issues and equally successful results.

Cheers,
Kevin

kkieger · April 24, 2020, 7:45pm

Melissa,

I had posted the following under separate topic, but has now gone without a response for over a week. My apologies if this isn’t proper in the support forum, but I’m still struggling with the solution.

EnterpriseDNA has some really good videos on custom financial calendars. I’ve found the ones where the SELECTEDVALUE function is used as Variables in determining Previous Year to Date (PYTD) with custom fiscal calendars. Sales YTD and Sales PY are working correctly with the fiscal 4-4-5 calendar. However, I’ve been unable to translate those teachings into a valid “Sales PYTD - Fiscal445” measure.

Enterprise DNA (Melissa, in particular) has been exposed to this PBIX file recently in producing Increase/Decrease/No Change counts in YTD and PYTD comparisons based on a standard calendar. The objective now is to produce the same measures (PYTD, in particular) with a custom 4-4-5 fiscal calendar.

Specifically per the attached, my measure of “Sales PYTD - Fiscal445” produces a valid total for fiscal year 2020 (and all Sales reps). However, the individual rows in the Matrix visualization per Product ID do not display the values which sum to the total… some individual values display, some do not. Similarly, when a Sales Rep is selected in the Slicer, the total is also incorrect.

With these Slicer selections (Fiscal Year = 2020, Sales Rep = Paige Turner) - this Orders table record should be included in the “Sales PYTD - Fiscal445” calculation:
Order ID: CA-2015-1578125400
Product ID: OFF-BI-10000285
Ship Date: 3/26/2019
It is not included, and I haven’t stumbled into the “Sales PYTD - Fiscal445” logic which is correct.

PBIX and its Excel data source are attached. Of note, I’ve taken the documents provided by your EnterpriseDNA colleague and produced a Sales PYTD v2 - Fiscal445 measure which also does not work as desired.

I appreciate the help.

Best regards,
Kevin

Sample DNA Dataset for PYTD with Custom Fiscal Calendar.pbix (221.1 KB)
PowerBI_Sample_Dataset.xlsx (46.7 KB)