cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Frequent Visitor

Preventing SAMEPERIODLASTYEAR from impacting one of my Measures

Hi,

 

I have a variable Current USD Sales -Monthly that looks like this:

 

Current USD Sales - Monthly =

VAR betweendates=DATESBETWEEN(tblCalendar[Date],[display start],[display end])
VAR tenantvals=VALUES('Ex Tenants'[Ex Tenants])

return

IF(ISFILTERED('Publicly Reported'[Publicly Reported]),
CALCULATE(SUM(v_BHOSales[iSales2]),
 
betweendates,
KEEPFILTERS(v_BHOSales[Public Reporting]="Public"),
KEEPFILTERS(NOT(v_BHOSales[stTradeName] IN tenantvals))),

CALCULATE(SUM(v_BHOSales[iSales2]),
betweendates,
KEEPFILTERS(NOT(v_BHOSales[stTradeName] IN tenantvals)))
)
 
I would like to add a filter to this calculation that determines whether or not this calculation is being performed on a row where the column Comp Flag is equal to "Comp" if I select it in the slicer. I have a similar calculation for prior period where I recalculate this using SAMEPERIODLASTYEAR.
 
Prior USD Sales - Monthly 2 = CALCULATE([Current USD Sales - Monthly],SAMEPERIODLASTYEAR(tblCalendar[Date]))
 
The problem is that if a row value was "Comp" in the current year, but not the prior year, the prior calculation will be blank. I would like for the prior period to calculate if the current period is "Comp". How do I get the SAMEPERIODLASTYEAR function to ignore the Comp column or how do I create a measure that lets me ignore it? I need to be able to do this for whatever period is considered current based on a date slicer selection. Thanks!
4 REPLIES 4
Super User IV
Super User IV

@tfeldt2020 , not sure I got it.

Assume the measures are Curr period and last period

Try something like

if (max(Table[Col]="Comp",[Curr period],[last period])



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Thanks for your response! It looks like your if statement is alternating the measures based on the value of the column. What I want is for the prior sales field to calculate based on the row value of the column Comp as long as the row value matches the Comp slicer. Right now, when I filter on the Comp field, I get a blank value for the prior calculations because they were "Non Comp" during the prior period. I want to be able to compare the two values as long as the current period Comp value matches the slicer filter.

Hi @tfeldt2020 

 

Do you still have issues with this?

If you've fixed the issue on your own please kindly share your solution. If any of the above posts helped, please kindly mark it as a solution to help others find it more quickly. Thanks!

Cheers,
Sturla

Hi @tfeldt2020 

 

you might consider creating pbix file that will contain some sample data (remove the confidential info), upload the pbix to onedrive for business and share the link to the file. Please do not forget to describe the expected results based on this sample data.

 

Community Support Team _ Dina Ye
If this post helps, then please consider Accept it as the solution to help the other members find it more
quickly.

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Microsoft Ignite

Microsoft Ignite with Arun Ulag

Featured Session: Drive Data Culture with Power BI- Vision, Strategy & Roadmap. Register here https://myignite.microsoft.com #MSIgnite #PowerPlatform #Power BI ​

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors