Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Kopek
Helper IV
Helper IV

measure filtering month range

Hi!

 

I have a below dataset:

 

LOBPortfolioProductJan-20Feb-20Mar-20Apr-20May-20Jun-20Jul-20Aug-20Sep-20Oct-20Nov-20Dec-20
Test 1Prt 1Prod 1100011001200130014001500160017001800190020002100
Test 1Prt 2Prod 2200021002200230024002500260027002800290030003100
Test 1Prt 3Prod 3300031003200330034003500360037003800390040004100
Test 1Prt 4Prod 4400041004200430044004500460047004800490050005100
Test 2Prt 5Prod 5500051005200530054005500560057005800590060006100
Test 2Prt 6Prod 6600061006200630064006500660067006800690070007100
Test 2Prt 7Prod 7700071007200730074007500760077007800790080008100
Test 2Prt 8Prod 8800081008200830084008500860087008800890090009100
Test 2Prt 9Prod 990009100920093009400950096009700980099001000010100

 

 

and I try to create measure showing my actuals  and forecast, and i have no idea what i do wrong!

 

Actuals = should be sum of January 2020 (in Feb it should be sum of Jan + Feb, and so on)

Forecast = should be sum of all months starting Feb till Dec (in Feb it should be all months March untill Dec  and so on)

 

I tired YTD, Claculate, and nothing works.

I unpivotted Jan-Dec columns, so instead  that i have attribute and value columns.

 

Please let me know which measure will work for me in that case!

 

Thanks in advance!

 

1 ACCEPTED SOLUTION

Hi @Kopek ,

 

check this out.

PBIX

 

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


View solution in original post

2 REPLIES 2

Hi @Kopek ,

 

check this out.

PBIX

 

Regards,

Marcus

Dortmund - Germany
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.

Did I answer your question?
Please mark my post as solution, this will also help others.
Please give Kudos for support.

Marcus Wegener works as Full Stack Power BI Engineer at BI or DIE.
His mission is clear: "Get the most out of data, with Power BI."
twitter - LinkedIn - YouTube - website - podcast


amitchandak
Super User
Super User

First of all unpivot data.

https://radacad.com/pivot-and-unpivot-with-power-bi

 

then from month create month-end date. And add a date calendar so that datesytd or totalytd can work.

For Feb to Dec use datesinperiod using the end of year take it dec and use rolling 11

example

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
Rolling 11 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFYEAR(Sales[Sales Date]),-11,MONTH))  


Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(sales,sales[date] <=maxx(date,date[date])))

 

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/

 

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.