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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
itscaptainmatt
Regular Visitor

Same month, last year with a slicer on page

Hi,

 

I've tried many ways of doing this with sameperiodlast year and the dateadd functions but I can't seem to eliminate the effect of my slicer. I have a slicer that allows me to choose which month and year.

 

I am able to to total my sales in column 1, pull my sales target in column 2 and have my pace % for current month in column 3. I would like to add my sales of the same month but year selected -1. For column 5 I would like to have Column 1 divided by column 4 (should be ok once I figure out column 4. Example of what I would like:

 

CategorySalesTargetPace %Sales Last YearLast Year %

 

My slicer affects my entire visual and I can't seem to figure out how to have it not affect this particular measure. My Sales Last Year Column ends up showing the same data as my Sales column.

 

Could I please have some help with this?

1 ACCEPTED SOLUTION
v-zhouwen-msft
Community Support
Community Support

Hi @lbendlin ,thanks for the quick reply, I'll add further.

Hi @itscaptainmatt ,

The Table data is shown below:

vzhouwenmsft_0-1713952901410.png

Please follow these steps:
1. Use the following DAX expression to create a measure

Last Year = 
VAR _a = SELECTEDVALUE('Table'[Year]) -1 
VAR _b = SELECTEDVALUE('Table'[Month])
VAR _c = CALCULATE(SUM('Table'[Sales]),'Table'[Year] = _a,'Table'[Month] = _b)
RETURN _c

 2.Final output

vzhouwenmsft_1-1713953041687.png

 

View solution in original post

2 REPLIES 2
v-zhouwen-msft
Community Support
Community Support

Hi @lbendlin ,thanks for the quick reply, I'll add further.

Hi @itscaptainmatt ,

The Table data is shown below:

vzhouwenmsft_0-1713952901410.png

Please follow these steps:
1. Use the following DAX expression to create a measure

Last Year = 
VAR _a = SELECTEDVALUE('Table'[Year]) -1 
VAR _b = SELECTEDVALUE('Table'[Month])
VAR _c = CALCULATE(SUM('Table'[Sales]),'Table'[Year] = _a,'Table'[Month] = _b)
RETURN _c

 2.Final output

vzhouwenmsft_1-1713953041687.png

 

lbendlin
Super User
Super User

Use disconnected tables to feed your slicers.

 

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.