So I have a single global filter with yr,qtr,month.The columns don't use the date calendar in power BI.I have the columns date(Type:date), qtr(Type:whole number), Month(Type:whole number), year(Type:whole number).
I need the values for current and previous year for YOY % change.If the selected current year from the filter is actually the current year then I want my previous year to calculate only for the data untill last year today's date.And the values should change properly when I select year,quarter or month.I'm using the conditions:
return if (
selected_prev_year<>Original_prev_yr,
//for all the selected current yr < 2023 as we'll have data for all months
alllogic,
//calculates for the required metric between min date and max date of selected year-1.
if (
And(
prev_year=Original_prev_yr,
MindateInQuarter<=prev_end_date
//for actual current yr as the data will be available only till Today(),I added the 2nd condition because if I select quarter then it was showing the values of 2nd quarter for 3rd and 4th also.Whereas it should be blank.
prev_end_date=date made from todays date & month and selecetd-1 yr.
MindateInQuarter=min date for selected quarter
),
curryearlogic,
//calculates for the required metric between min date to today's date of selected year-1.
if(
AND( prev_year=Original_prev_yr,
MaxdateInSelectedQuarter < MindateInNextQuarter
//Now only after the 2nd condition is true then this should be checked that if I select a quarter then the calculations should be max date of quarter should be only till the max date of quarter.
),
forQuarter
//calculates for the required metric untill max date of selected quarter for selecetd yr-1.
)
))
I have cross checked if the dates in my variables are correct except for 'MindateInNextQuarter'.
var MindateInNextQuarter = CALCULATE(min(d_date_c[day_dt]),d_date_c[cal_qtr_nbr]=SELECTEDVALUE(d_date_c[cal_qtr_nbr])+1,d_date_c[cal_yr_nbr] = SELECTEDVALUE(d_date_c[cal_yr_nbr])-1)
If I return this it gives blank even tho the data type of cal_qtr_nbr is whole number.All other date variables return correct dates.
I am getting the same value when I select on 2023,1st quarter and all th emonths in the 1st quarter.I'm getting different value for 2nd quarter which is correct but then the same value for all the months in the second quarter.
How I've calculated other variables:var prev_year_min_date = CALCULATE(MIN(d_date_c[day_dt]),ALL(d_date_c[cal_yr_nbr],d_date_c[cal_qtr_nm],d_date_c[cal_mth_nbr]),REMOVEFILTERS(d_date_c[cal_yr_nbr],d_date_c[cal_qtr_nm],d_date_c[cal_mth_nbr]),d_date_c[cal_yr_nbr] = SELECTEDVALUE(d_date_c[cal_yr_nbr])-1)
And subsequently others.
Can any one please suggest how do I get the correct values for quarter and month if the prev year=actual prev year.
Thankyou so much for your time!
