First N month of year adding one month each 30 days

[NB3]

Hello everyone,

My title is kinda tricky so here is what I need.

I'm currently working on some financial report and I'm stuck with my YTD budget and YTD 2019.

We always have the numbers 2 months back (In June we have April, In July we have May. etc.)

Now YTD actual 2020 is no problem but it is when it comes to budget 2020 and actual 2019 since we have the numbers for the whole year.

I can't use a YTD formula since I don't want to see May and June and can't use a formula for the first N months of the year since I need one month to be added each month.

Is there any DAX formula that allows me to show let's say the first 4 months of the year (Jan-April) in June and once in July the first 5 months (Jan-May) without me having to manipulate anything?

Hopefully that makes sense.

Thanks,

Nick

[amitchandak · Accepted solution]

@NB3 , try like with date table

Example

YTD QTY forced= 
var _max = eomonth(today(),-2)
return
calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),'Date'[Date]<=_max)
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

LYTD QTY forced= 
var _max = date(year(today())-1,month(today())-2,day(today()))
return
CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max)
//TOTALYTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,year),'Date'[Date]<=_max)

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[ryan_mayu]

@NB3 

I created a sample data to test.

and I also create a calendar table.

Measure = 
var maxdate=max(FC[date])
VAR datebegin=EOMONTH(maxdate,-6)
VAR dateend=EOMONTH(maxdate,-2)
return if(year(datebegin)<>year(today()),CALCULATE(sum(FC[FC]),FILTER(all('FC'),FC[date]>=date(year(today()),1,1)&&'FC'[date]<=dateend)),cALCULATE(sum(FC[FC]),FILTER(all('FC'),FC[date]>=datebegin&&'FC'[date]<=dateend)))

I tried to select Jun2020, result = 1000+2000+3000+4000(jan to Apr 2020)

Then tried to select May, result = 1000+2000+3000(jan to mar 2020)

Hope this is helpful