Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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

Hi All,

 

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

Screenshot 2021-08-22 235853.png

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 StoresStore NameSales RepBrandnamecategorycityRevenuedatesmonth namemonthnum
Parent AStore AJoePenCategory A Toronto6.9509-Jul-21July7
Parent AStore AJoePenCategory A Toronto6.9518-Jun-20June6
Parent AStore BAdamPenCategory A Boston6.9506-Aug-21August8
Parent BStore CRandyPencilsCategory A Los Angeles7.8501-Jul-21July7
Parent BStore CRandyEraserCategory A Los Angeles8.7626-Jul-21July7
Parent BStore CRandyEraserCategory A Los Angeles5.6725-Jun-21June6
Parent BStore DAdamMarkerCategory C New York5.6719-Jul-21July7
Parent CStore EJoeBooksCategory B Hamilton67.9523-Jul-21July7
Parent CStore FAdamBooksCategory B Miami76.2113-Jul-21July7
Parent CStore FAdamBooksCategory B Miami5.6727-Jul-21July7
Parent CStore FAdamPencilsCategory A Miami4.3212-Jun-21June6
Parent CStore FAdamMarkerCategory C Miami5.6718-Jun-21June6

 

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.

 

10 REPLIES 10
SanketBhagwat
Solution Sage
Solution Sage

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], -12MONTH ) ).

 

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

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.

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)
    )
)

vkkfmsft_0-1629970315536.png

 

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.

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 A1007567%60%
Store B505033.30%40%

 

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

 

 

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

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?

Screenshot 2021-09-03 165827.png

 

number_brands_YTD =
CALCULATE(
DISTINCTCOUNT('table'[BrandName]),
FILTER(
ALL('Calendar'),
'Calendar'[Date] >= EDATE( DATE( YEAR(TODAY()), 1, 1 ), 2 )
&& 'Calendar'[Date] <= MAX('Calendar'[Date])
)
)

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] )
)

image.png

 

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.

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.

 

Screenshot 2021-09-10 161143.png

 

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

 

Thanks,

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.

vkkfmsft_1-1631608573624.png

vkkfmsft_0-1631608518828.png

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

vkkfmsft_2-1631608610452.png   vkkfmsft_3-1631608657397.png

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] )
    )
)

 

vkkfmsft_4-1631609336022.png

 

Best Regards,
Winniz

 

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') )

 image.png

 


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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.