PARALLELPERIOD and non-contiguous dates in source data?

Guitarmageddon

Board Regular
Joined
Dec 22, 2014
Messages
161
Hey folks, doing some learning today about the PARALLELPERIOD function and it seems to be sticking so far. However, there was some case study data used in the course Im taking and it got me thinking to some ways to apply PARALLELPERIOD.

Can you only use it if you have contiguous dates in the source data and/or date lookup table? Here is a snip of the raw data (USDA crop volume) as I pull it in to a table visual. You can see, it lists a full calendar date in the raw data, but the supplied data is only for January 1 of each year. Would PARALLELPERIOD not work in this case?
1692793463067.png

and the raw data (broken down by commodity name, but its all only ever listed for a "Year" of January 1, XXXX
1692793517697.png


In my mind, I "should" be able to write something like this, correct? For indicating the change in commodity volume vs the prior year... However, you can see that it just duplicates the value all the way down. Is there something with the way I am writing the measure to do this? Or do I need to nest the "Crops[Year]" inside of a YEAR function to truly pull out the year? Confused... thanks all
1692794867069.png
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
Ok small update. I used "CALENDAR AUTO" to add a calendar into the model, and then connected it to the one Raw data table. It then allowed the measure to perform as I would expect. However, it only works when selecting the "Year" field of the raw data (which is just that date/time stamp of January 1 of each year). If I attempt to use the master calendar table for anything, it behaves just as it did above and duplicates the value.

Here you can see it working correctly. It is subtracting the sum of crop volume from the prior year from the current year.
1692796395013.png


But now if I were to drag the YEAR from the master date table I added via CALENDARAUTO, you get the old behavior. And that even changes the behavior from my measure above too!
1692796558863.png
 
Upvote 0
Ok....taking you folks along my learning journey in case anyone else stumbles through as I have. Also, feel free to leave any remarks or things that "should" be obvious but I just missed due to knowledge base.

I added the master date table via CALENDARAUTO, marked that as a date table in the data model, and also added a second column using the YEAR function within it (which had to be set as a text value)

Once I did this, I was then able to use the master date table's date within my function (below portion changed in orange), and then it behaved as it should
1692799001541.png
 

Attachments

  • 1692798925528.png
    1692798925528.png
    88.8 KB · Views: 4
Upvote 0

Forum statistics

Threads
1,223,945
Messages
6,175,556
Members
452,652
Latest member
eduedu

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top