Forum Discussion
Helper IVmeasure filtering month range
Hi!
I have a below dataset:
| LOB | Portfolio | Product | Jan-20 | Feb-20 | Mar-20 | Apr-20 | May-20 | Jun-20 | Jul-20 | Aug-20 | Sep-20 | Oct-20 | Nov-20 | Dec-20 |
| Test 1 | Prt 1 | Prod 1 | 1000 | 1100 | 1200 | 1300 | 1400 | 1500 | 1600 | 1700 | 1800 | 1900 | 2000 | 2100 |
| Test 1 | Prt 2 | Prod 2 | 2000 | 2100 | 2200 | 2300 | 2400 | 2500 | 2600 | 2700 | 2800 | 2900 | 3000 | 3100 |
| Test 1 | Prt 3 | Prod 3 | 3000 | 3100 | 3200 | 3300 | 3400 | 3500 | 3600 | 3700 | 3800 | 3900 | 4000 | 4100 |
| Test 1 | Prt 4 | Prod 4 | 4000 | 4100 | 4200 | 4300 | 4400 | 4500 | 4600 | 4700 | 4800 | 4900 | 5000 | 5100 |
| Test 2 | Prt 5 | Prod 5 | 5000 | 5100 | 5200 | 5300 | 5400 | 5500 | 5600 | 5700 | 5800 | 5900 | 6000 | 6100 |
| Test 2 | Prt 6 | Prod 6 | 6000 | 6100 | 6200 | 6300 | 6400 | 6500 | 6600 | 6700 | 6800 | 6900 | 7000 | 7100 |
| Test 2 | Prt 7 | Prod 7 | 7000 | 7100 | 7200 | 7300 | 7400 | 7500 | 7600 | 7700 | 7800 | 7900 | 8000 | 8100 |
| Test 2 | Prt 8 | Prod 8 | 8000 | 8100 | 8200 | 8300 | 8400 | 8500 | 8600 | 8700 | 8800 | 8900 | 9000 | 9100 |
| Test 2 | Prt 9 | Prod 9 | 9000 | 9100 | 9200 | 9300 | 9400 | 9500 | 9600 | 9700 | 9800 | 9900 | 10000 | 10100 |
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!
2 Replies
- amitchandak
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
