Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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
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?
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
@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 ?
@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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |