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
tfeldt2020
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
amitchandak
Super User
Super User

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

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