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

Multiple Selections for HASONEVALUE - Prior Periods

Hey Community!

 

I have the following code I found online to provide me Prior Period calculations based on a slider.  It works perfectly if you select one Month/Quater/Year.  However, it fails when selecting multiple periods within the Month/Quarter/Year.  Is there a way I change the code to get the desired results?  I have tried replacing "HASONEVALUE" with "VALUES" and I get an error.  Thank you in advance for your help!

 

Conc SM Gross PP:=
VAR DateCount =
COUNTROWS ( 'Report Date' )
VAR PeriodType =
SWITCH (
TRUE (),


AND (
HASONEVALUE ('Report Date'[CalendarYearID]),
DateCount = COUNTROWS ( PARALLELPERIOD ('Report Date'[Date], 0, YEAR ) )
), "Year",

AND (
HASONEVALUE ('Report Date'[CalendarQuarterID] ),
DateCount = COUNTROWS ( PARALLELPERIOD ( 'Report Date'[Date], 0, QUARTER ) )
), "Quarter",

AND (
HASONEVALUE ('Report Date'[CalendarMonthID] ),
DateCount = COUNTROWS ( PARALLELPERIOD ('Report Date'[Date], 0, MONTH ) )
), "Month",

AND (
HASONEVALUE ('Report Date'[CalendarYearID] ),
DateCount = COUNTROWS ( DATESYTD ('Report Date'[Date] ) )
), "YTD",


AND (
HASONEVALUE ('Report Date'[CalendarQuarterID]),
DateCount = COUNTROWS ( DATESQTD ( 'Report Date'[Date] ) )
), "QTD"

)
RETURN
SWITCH (
PeriodType,

 

"Year", CALCULATE ( [Conc SM Gross], PREVIOUSYEAR ('Report Date'[Date]) ),
"Quarter", CALCULATE ( [Conc SM Gross], PREVIOUSQUARTER ( 'Report Date'[Date] ) ),
"Month", CALCULATE ( [Conc SM Gross], PREVIOUSMONTH ('Report Date'[Date] )),
"YTD", CALCULATE ( [Conc SM Gross], PREVIOUSYEAR( 'Report Date'[Date] )),
"QTD", CALCULATE ( [Conc SM Gross], PREVIOUSQUARTER('Report Date'[Date] ) )


)

1 REPLY 1
Community Support Team
Community Support Team

Re: Multiple Selections for HASONEVALUE - Prior Periods

Hi @bigman24 ,

 

Do you have some sample data or preferably a sample pbix file.

 

Read this post to get your answer quickly.

https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

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
New Ranks and Rank Icons in 2020

New Ranks and Rank Icons in 2020

Read the announcement for more information!

New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

November 2019 Community Highlights

November 2019 Community Highlights

Get an overview of the events and great community content from November.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)