Current period vs Previous Period and Fiscal YTD vs. Previous YTD based on date and product slicers

[user_unknown39]

Hi All,

I am very new to Power BI and am trying to create the following. 

I have slicers for Sales Rep, Brands, City and Account name and a slider for dates and I would like to see the revenue in the tables based on the slicers.  My data goes back to 3 years. I have created a date table for my fiscal calendar, which runs from March- Feb.

My data looks as follows.

Parent Stores	Store Name	Sales Rep	Brandname	category	city	Revenue	dates	month name	monthnum
Parent A	Store A	Joe	Pen	Category A	Toronto	6.95	09-Jul-21	July	7
Parent A	Store A	Joe	Pen	Category A	Toronto	6.95	18-Jun-20	June	6
Parent A	Store B	Adam	Pen	Category A	Boston	6.95	06-Aug-21	August	8
Parent B	Store C	Randy	Pencils	Category A	Los Angeles	7.85	01-Jul-21	July	7
Parent B	Store C	Randy	Eraser	Category A	Los Angeles	8.76	26-Jul-21	July	7
Parent B	Store C	Randy	Eraser	Category A	Los Angeles	5.67	25-Jun-21	June	6
Parent B	Store D	Adam	Marker	Category C	New York	5.67	19-Jul-21	July	7
Parent C	Store E	Joe	Books	Category B	Hamilton	67.95	23-Jul-21	July	7
Parent C	Store F	Adam	Books	Category B	Miami	76.21	13-Jul-21	July	7
Parent C	Store F	Adam	Books	Category B	Miami	5.67	27-Jul-21	July	7
Parent C	Store F	Adam	Pencils	Category A	Miami	4.32	12-Jun-21	June	6
Parent C	Store F	Adam	Marker	Category C	Miami	5.67	18-Jun-21	June	6

I have tried a few different ways, but my data doesn't appear correctly in the table format. Ideally, when we click on the store in the first table, we would get a list of all the brands sold at that store with their respective revenues.

Any help would be greatly appreciated.

Thank-you.

[SanketBhagwat]

Hi @user_unknown39 .

You can try the below DAX to get the desired output.

1)Current Period=CALCULATE(SUM(Table1[Column]),YEAR(Table1[date]=YEAR(TODAY( ) )).

2)LAst Year Same Period= CALCULATE(SUM(Table1[Column]),SAMEPERIODLASTYEAR(Table1[date]).

3)YTD =CALCULATE(SUM(Table1[Column]),DATESYTD(Table1[date]).

4)Last Year YTD=TOTALYTD ( SUM ( Table1[Column] ), DATEADD ( table[date], -12, MONTH ) ).

Please let me know if it helps.

If this post helps answer your question, then please do mark it as 'Accept as Solution' and give it a big thumbs up.

Reagards,

Sanket Bhagwat

[user_unknown39]

Hi @SanketBhagwat ,

I don't this will work as your code applies only to only the year. I am looking at the date/day level. 

So for example: If the date selected in the slider is from 04/01/2021- 06/31/2021, I would like to see the numbers for the selected period vs. the same period last year. For YTD, I will see the numbers from 03/01/2021 to 06/31/2021 as my fiscal year starts in March.

[v-kkf-msft]

Hi @user_unknown39 ,

Try the following formula:

Current period = SUM('Table'[Revenue])

Last Year Same Period = 
CALCULATE(
    SUM('Table'[Revenue]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Date] >= EDATE( MIN('Calendar'[Date]), -12 ) 
        && 'Calendar'[Date] <= EDATE( MAX('Calendar'[Date]), -12 )
    )
)

YTD = 
CALCULATE(
    SUM('Table'[Revenue]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Date] >= EDATE( DATE( YEAR(TODAY()), 1, 1 ), 2 )
        && 'Calendar'[Date] <= MAX('Calendar'[Date])
    )
)

Last Year YTD = 
CALCULATE(
    SUM('Table'[Revenue]),
    FILTER(
        ALL('Calendar'),
        'Calendar'[Date] >= EDATE( DATE( YEAR(TODAY()) - 1, 1, 1 ), 2 )
        && 'Calendar'[Date] <= EDATE( MAX('Calendar'[Date]), -12)
    )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[user_unknown39]

Thanks @v-kkf-msft. How would this work with percent revenue share by the store?

I am looking to calculate the total revenue share by store

eg:

Store Name	Current Period	Last Year Same Period	Current Share	LY Share
Store A	100	75	67%	60%
Store B	50	50	33.30%	40%

Thank you once again and I greatly appreciate all your help.

[v-kkf-msft]

Hi @user_unknown39 ,

Has your problem been solved? If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

[user_unknown39]

Hi @v-kkf-msft , Based on your comments, I created the following to calculate distinct counts as well. The rows look fine but the grand total at the bottom doesn't show the sum correctly. Can this be fixed?

number_brands_YTD =

CALCULATE(

DISTINCTCOUNT('table'[BrandName]),

FILTER(

ALL('Calendar'),

'Calendar'[Date] >= EDATE( DATE( YEAR(TODAY()), 1, 1 ), 2 )

&& 'Calendar'[Date] <= MAX('Calendar'[Date])

)

)

[v-kkf-msft]

Hi @user_unknown39 ,

Please try to use HASONEVALUE(<columnName>) or ISFILTERED(<columnName>) to change the value of the total part. Like this:

Measure = 
IF(
    HASONEVALUE('Table'[Parent Stores]),
    [number_brands_YTD],
    SUMX( ALLSELECTED('Table'[Parent Stores]), [number_brands_YTD] )
)

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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

[user_unknown39]

Hi @v-kkf-msft ,

The row totals where number_brands_YTD is null are showing up wrong. I used your code to create the second column (number of listings). The rows in column 2 that is showing the grand total should actually be null.

Please see the details below.

I hope you can help me resolve this issue at the earliest.

Thanks,

[v-kkf-msft]

Hi @user_unknown39 ，

This is due to context. I was able to reproduce your problem if I used the Parent Stores field in different tables.

You need to use the Parent Stores field from the same table. 

   

If the problem still hasn't been solved, try adding the condition: IF( [number_brands_YTD] <> BLANK(), ....

Like this:

Measure = 
IF(
    [number_brands_YTD] <> BLANK(),
    IF(
        HASONEVALUE('Table'[Parent Stores]),
        [number_brands_YTD],
        SUMX( ALLSELECTED('Stores'[Parent Stores]), [number_brands_YTD] )
    )
)

Best Regards,
Winniz

[v-kkf-msft]

Hi @user_unknown39 ,

I think you need to create the following measure:

Current Share = [Current period] / SUMX( ALLSELECTED('Table'), [Current period] )

LY Share = [Last Year Same Period] / CALCULATE( [Last Year Same Period], ALLSELECTED('Table') )

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.

Best Regards,
Winniz

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