Column Formula for Last completed custom time period

[jpt1228]

Hello - I have a DimDate table that has all the columns for calendar granularity - Year Complete, Quarter Complete, Month Complete, Week Complete, Day Complete - But I have a custom calendar with 13 periods that are 4 weeks in duration. In 2020 the fiscal year starts on 12/29/2019 and ends 1/2/2021.

I have columns for Calendar Date, Fiscal Year, Fiscal Date Start, Fiscal Date End, Fiscal Days elapsed, Fiscal week in year, as well as others. 

I wanted to add a column or measure to return dollar sales but not include any incomplete fiscal periods. I want to be able to build visuals for sales by fiscal period/year but not show any incomplete fiscal periods.

[v-yuta-msft]

@jpt1228 ,

You need to build a slicer/visual level filters to filter the complete fiscal date or create measure using function like FILTER(). 

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Check if this can help

Power-BI-Working-with-Non-Standard-Time-Periods

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges

Connect on Linkedin

[jpt1228]

Hello @amitchandak  I reviewed the link you recommended. I know how to calculate sales for current period and same time last year etc. What I am trying to do is create a column that will return a date filter for the most recent completed fiscal year/period. Then I can use as a page filter for lastperiodcompleted = true and ignore all other time periods. Something like the below for IsCurrentFiscalPriod but would subtract 1 period from the current fiscal period and return true for that period. this custom column is not working.

IsPreviousFiscalPeriod =

VAR TodaysPeriod = MINX(FILTER(DimDate, DimDate[Date] =

TODAY()), DimDate[Fiscal Year Period Sort])

RETURN

IF((DimDate[Fiscal Year Period Sort] -1), = TodaysPeriod, TRUE(), FALSE())

[amitchandak]

When you say not working. What it is giving.  The formula seems fine. Do following

1. Check IsPreviousFiscalPeriod = VAR TodaysPeriod = MINX(FILTER(DimDate, DimDate[Date] =TODAY()), DimDate[Fiscal Year Period Sort])
what does this gives

2. Check IsPreviousFiscalPeriod = VAR TodaysPeriod = MINX(FILTER(DimDate, DimDate[Date] =TODAY()), DimDate[Fiscal Year Period Sort]) -1, is the one you wanted

3. Check if this give correct result

IsPreviousFiscalPeriod =
VAR TodaysPeriod = MINX(FILTER(DimDate, DimDate[Date] =
TODAY()), DimDate[Fiscal Year Period Sort])
RETURN
IF((DimDate[Fiscal Year Period Sort] -1), = TodaysPeriod, 1, 0)

Or

IsPreviousFiscalPeriod =
VAR TodaysPeriod = MINX(FILTER(DimDate, DimDate[Date] =
TODAY()), DimDate[Fiscal Year Period Sort]) -1
RETURN
IF((DimDate[Fiscal Year Period Sort]), = TodaysPeriod, TRUE(), FALSE())

IsPreviousFiscalPeriod =
VAR TodaysPeriod = MINX(FILTER(DimDate, DimDate[Date] =
TODAY()), DimDate[Fiscal Year Period Sort]) -1
RETURN
IF((DimDate[Fiscal Year Period Sort]), = TodaysPeriod, 1, 0)

If it still does not work, give some sample data for that table.

[jpt1228]

Hello @amitchandak  The Result is CurrentFiscalPeriod - 1 but this result is period 0 and there is no period 0. We are currently in 202001 which is 2020 fiscal period 1. The previous period would be year 2019 fiscal period 13. I would need the result to be 201913.