cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Anonymous
Not applicable

## Fiscperyear -2022012 /2022011 for YTD MTD PREVIOUSYEAR calculation

Hi All,

I have no date column in my dataset and only fiscalyearperiod which contains data as string with values like 2022012,2022011 .

How do I perform calculation for YTD ,Previous year ,  MTD and other date related calculations ?

Thanks

1 ACCEPTED SOLUTION
Community Support

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

``````Date =
CALENDAR(
DATE(2021,1,1),DATE(2023,12,31))``````

Create calculated column

``````YearMonth =
YEAR('Date'[Date])&"0"&FORMAT('Date'[Date],"mm")``````

2. Create measure.

``````slicer year =
var _select=SELECTCOLUMNS('Date',"YEARMONTH",[YearMonth])
return
SUMX(
FILTER(ALL('Table'),
'Table'[fiscalyearperiod]=MAX('Table'[fiscalyearperiod])&&'Table'[fiscalyearperiod] in _select),
[value])``````
``````last year =
var _select=SELECTCOLUMNS('Date',"YEARMONTH",[YearMonth])
var _date=
MAXX(
FILTER(ALL('Date'),
'Date'[YearMonth]=MAX('Table'[fiscalyearperiod])),[Date])
var _Lastyear=
DATE(
YEAR(_date)-1,MONTH(_date),DAY(_date))
var _lastyearmonth=
MAXX(
FILTER(ALL('Date'),'Date'[Date]=_Lastyear),[YearMonth])
return
IF(
MAX('Table'[fiscalyearperiod]) in _select,
SUMX(
FILTER(ALL('Table'),'Table'[fiscalyearperiod]=_lastyearmonth),[value]))``````

3. Result:

Best Regards,

Liu Yang

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

4 REPLIES 4
Super User

Hi,

What is 2022012 - is it December 2022?  Please clarify.  Share data in a format that can be pasted in an MS Excel file.  Does the FY start from January?

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

Hi  @Anonymous ,

I created some data:

Here are the steps you can follow：

1. Create calculated table.

``````Date =
CALENDAR(
DATE(2021,1,1),DATE(2023,12,31))``````

Create calculated column

``````YearMonth =
YEAR('Date'[Date])&"0"&FORMAT('Date'[Date],"mm")``````

2. Create measure.

``````slicer year =
var _select=SELECTCOLUMNS('Date',"YEARMONTH",[YearMonth])
return
SUMX(
FILTER(ALL('Table'),
'Table'[fiscalyearperiod]=MAX('Table'[fiscalyearperiod])&&'Table'[fiscalyearperiod] in _select),
[value])``````
``````last year =
var _select=SELECTCOLUMNS('Date',"YEARMONTH",[YearMonth])
var _date=
MAXX(
FILTER(ALL('Date'),
'Date'[YearMonth]=MAX('Table'[fiscalyearperiod])),[Date])
var _Lastyear=
DATE(
YEAR(_date)-1,MONTH(_date),DAY(_date))
var _lastyearmonth=
MAXX(
FILTER(ALL('Date'),'Date'[Date]=_Lastyear),[YearMonth])
return
IF(
MAX('Table'[fiscalyearperiod]) in _select,
SUMX(
FILTER(ALL('Table'),'Table'[fiscalyearperiod]=_lastyearmonth),[value]))``````

3. Result:

Best Regards,

Liu Yang

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

Anonymous
Not applicable

@amitchandak All these calculaiton will be whole number right ?

How do I get selected FY trend along with last year same period trend in one chart ?

Super User

@Anonymous , Create a new table with these period

and these columns

fiscalyearperiod -- Taken from based table use to join back , Assume new table name is date

fiscalyearperiod Rank = rankx(Date, [fiscalyearperiod], ,asc,dense)

fiscalyear month = right([fiscalyearperiod],3)

fiscalyear year= right([fiscalyearperiod],4)*1

fiscalyear month Rank = rankx(Date, [fiscalyear month], ,asc,dense)

Then you can measures likes

This Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[fiscalyearperiod Rank]=max('Date'[fiscalyearperiod Rank])))
Last Month = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[fiscalyearperiod Rank]=max('Date'[fiscalyearperiod Rank])-1))

YTD= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[fiscalyear year]=max('Date'[fiscalyear year]) && 'Date'[fiscalyear month Rank] <= Max('Date'[fiscalyear month Rank]) ))
LYTD = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[fiscalyear year]=max('Date'[fiscalyear year])-1 && 'Date'[fiscalyear month Rank] <= Max('Date'[fiscalyear month Rank])))

If you need mtd

Power BI Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-Convert-to/ba-p/1657798

Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Announcements

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

#### Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors