YTD YOY measure without a date table

[alaynanich]

Hi! I have a matrix viz in my sales report that lists the sales years and months for our teams' vendors. One of the asks is to 1) filter the total sales for 2024 and 2023 for YTD based on the month, and 2) create YOY and YOY% columns based on YTD. I know how to do this with PBI's time intelligence functions, but the issue is the data comes only as months and years so there's no date columns. 

I tried to first make a calculated column finding the max month for 2025, then using that in the YOY measure to either look at 2024 or 2023 depending on what sales year you're lookign at. But it's only returning the actual sales amounts. any insight is appreciated - I would prefer this to be dynamic for any years.

YTD =
var maxmonth = MONTH(TODAY())
RETURN
IF('Total Vendor'[MonthNum] <= maxmonth, "Y", "N")
 
YTD YOY =
var curryear = 2025
var ty = CALCULATE(SUM('Total Vendor'[sales]), 'Total Vendor'[YTD]="Y")
var ly = IF(MAX('Total Vendor'[Year])=curryear,
    CALCULATE(SUM('Total Vendor'[sales]), 'Total Vendor'[Year] = 2024,'Total Vendor'[YTD]="Y"),
    CALCULATE(SUM('Total Vendor'[sales]), 'Total Vendor'[Year] = 2023,'Total Vendor'[YTD]="Y"))
return  ty-ly

[maruthisp]

Hi alaynanich,

I would sincerely suggest to have a DATE table either by using pbix date intelligence table or create one date table with DAX expression.
Use that table to calculate YTD, YOY measures.

NOTE: When there is gap for years or months or days in the actual data - without DATE table it doesn't work as expected.

Please let me know if you have further questions.

If this reply helped solve your problem, please consider clicking "Accept as Solution" so others can benefit too. And if you found it useful, a quick "Kudos" is always appreciated, thanks! 

Best Regards, 

Maruthi 

LinkedIn - http://www.linkedin.com/in/maruthi-siva-prasad/ 

X            -  Maruthi Siva Prasad - (@MaruthiSP) / X

[speedramps · Accepted solution]

Hi @alaynanich 

You have kind of answered the question yourself when you said 

"I know how to do this with PBI's time intelligence functions, but the issue is the data comes only as months and years so there's no date columns. "

It is best practice to use Power Query to transform the Year and Month columns to a date.

For example the start of year,  or end of year, or start or month or end of month.

This is easy to do it Power Query. 

You can then change the data type to date.

Then you relate the date to a calendar table and use standard YTD and Last year logic.

It is best practice to build and test datae intellegence once in a Clanedar table, then use it always.

Rather than faff about using dax date logic in Fact tables each time.

Here are some training links to help you:-

• https://www.youtube.com/watch?v=CJ2y6v4mQZk&pp=0gcJCdgAo7VqN5tD
• https://www.youtube.com/watch?v=XjVLaVLluYE
• https://www.youtube.com/watch?v=WBddNp_25YY

Please click thumbs up for the suggestions.

And clcik [accept solution] if it works.

You can accept more than 1 solution for a problem.

I want to help you more but your description is too vague.  

You will get a quicker and better response without misunderstandings if you put time and effort into carefully writing a clear problem description with example input and output data. Look forward to helping you when this information is forthcoming

* Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want. (That is just crazy). ‌‌
* Please DO give a simple non-technical functional description of what you want
* Keep it simple and break large projects into smaller questions and ask just one question per ticket.
* Rename columns to user friendly names. Avoid your own system jargon that we may not understand.
* Most importantly please provide example input data as table text (not a screen print) so helpers can import the data to build a solution for you. (Learn how to share data below)
* Provide the example desired output, with a clear step-by-step description of calculations and the process flow.
* Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.
* Remove any unneeded tables, rows or columns which may cause confusion. Keep it short and concise with the minimal information regarding the key problem.
* Remember not to share private data ... we don't want you to get into trouble. ‌‌
* Please click the thumbs up button for these helpful hints and tips. Thank you.

Learn how to attach data in the forum using OneDrive:-
* Save your file in a OneDrive folder
* Right click on the file and click the “Share” blue cloud icon
* Click the bottom “Copy” button
* Click” Anyone with link can edit”
* Click “Can Edit”
* Click “Can View”
* Click “Apply” button
* Click “Copy”
* Paste the generated link via the forum, email, chat, or any other method.
* Helpers can then download your data, build a solution and share it back.

Learn how to attach data in the forum using Dropbox:-
1. Open Dropbox: Access the Dropbox folder on your computer or through the Dropbox web interface.
2. Select File/Folder: Find the file or folder you want to share.
3. Click Share (or Get Link): Look for a "Share" option or a similar "Get Link" option.
4. Choose Permissions: Decide whether to allow "view only" or "view and download" access.
5. Copy and Share: Copy the generated link and share it with anyone via the forum, email, chat, or any other method.

[alaynanich]

Thanks @speedramps  I just tried your suggestion and the time calculations work now!