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
umpoohg
Helper I
Helper I

YTD Sales-Same Period Last Year

Hello All,

 

I have a requirement that I have been working on for the last week or so, basically YTD Sales/Cost with Same Period Last Year in a grid.  The report owner does not like how it has been created, as I have found that I need a slider/filter in order to get the TOTALYTD AND SAMEPERIODLASTYEAR  to work properly.  They do not want the user to have to select the date, as shown below.  Is there a way to get the DAX functions to work without a filter/slider?

 

ytdsalessply.PNG

12 REPLIES 12
pdamac
New Member

Dear friends, unfortunately this function has no solution if there is an error. It is the worst function of Power BI, because it is necessary but only works in an unknown condition and does not work in others. It's a pity that this happens in an excellent solution like Power BI. There will be no solution. If it works, use it if you do not use any other function. That's the truth.

dedelman_clng
Community Champion
Community Champion

Can you expand a little more on the requirement for the final data / visual ?  Also, if we can see a picture of the data model and some of your measures that you say aren't working, that would be helpful.

 

-David

HI David,

 

Thank you for responding.  The measures are working in my current configuration, however if I remove the slicers, the functions do not work as I would expect them to.  The business just wants to open the report, either via the PBI Service and the report to know what "Today" is, and thus just show the YTD sales, and then the amount of sales last year at the same point in time.  So, for example, I want to know what the sales this year, up to 03/23/2017 and then sales YTD for 03/23/2016

 

Here are the measures I am using:

 

YTD Sales = TOTALYTD(SUM('bi sales'[TotalLineSales]), DateTable[Date])

 

YTD Costs = TOTALYTD(SUM('bi sales'[TotalLineCost]), DateTable[Date])

 

SPLY Sales YTD = CALCULATE([YTD Sales], FILTER(ALL(DATETABLE),DateTable[Year]=MAX(DateTable[Year])-1),SAMEPERIODLASTYEAR(DateTable[Date]))

 

SPLY Costs YTD = CALCULATE([YTD Costs], FILTER(ALL(DateTable), DateTable[Year]=MAX(DateTable[Year])-1),SAMEPERIODLASTYEAR(DateTable[Date]))

 

datamodel.PNG

 

Please let me know if I can provide any more information.  Thank you, again.

 

@umpoohg First off, I think you have doubly filtered your LY measures.  SAMEPERIODLASTYEAR is an implict filter.

 

SPLY Sales YTD =
CALCULATE (
    [YTD Sales],
****NOT NEEDED --> FILTER ( ALL ( DATETABLE ), DateTable[Year] = MAX ( DateTable[Year] ) - 1 ),  <-- NOT NEEDED****
    SAMEPERIODLASTYEAR ( DateTable[Date] )
)

 

As to your requirements, I think that is going to depend on how the data is refreshed.  I have a similar report (that I have to refresh manually because of the data source), and I had to put in some extra M code (in the Edit Query area) to get the date table to only populate thru the current day:

 

...
(a) #"Changed Type" = Table.TransformColumnTypes(#"Inserted Start of Month",{{"Date", type date}}), (b) #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Date] <= Date.From(DateTime.LocalNow())) in (c) #"Filtered Rows"

 

**(a), (b) and (c) markers are there for reference only.  Do not put them in the code.

 

You would need to put (b) in via the "Advanced Editor".  Change "#Changed Type" in (b) to the value at the beginning of (a).  Change (c) to have the same name as the beginning of (b).

 

All that being said, since I have to manually refresh my data to get it up-to-date, I don't know if in a DirectQuery scenario (where the data is live, which is what I am inferring from your use case) the dates will update automatically with the rest of the transactional data.  You may have to build the date table in your data source.  Hopefully this should get you moving in the right direction.

 

Hope this helps,

David

Hi David,

 

The data will not be a DirectQuery scenario, as the data source will not allow that.  I have an enterprise gateway that will refresh the data once in the morning, so the data refresh shouldnt be a problem.

 

When I just created the measures, as I think I understand them, I get the same values for YTD and SAMEPERIODLASTYEAR, as shown below.

 

ytdsales is the same as splysalesytdsales is the same as splysales

 

ytdsales1 = TOTALYTD(SUM('bi sales'[TotalLineSales]), DateTable[Today's Date])

 

sply sales ytd_1 = CALCULATE([YTD Sales],SAMEPERIODLASTYEAR(DateTable[Date]))

 

YTD Sales = TOTALYTD(SUM('bi sales'[TotalLineSales]), DateTable[Date])

 

I am so confused now.... are you saying you have a report that has ytd values and sameperiodlastyear values on the same grid?

 

Thank you so much

 

 

Hi @umpoohg,

 

Could you try the formula below to see if it works in your scenario?Smiley Happy

YTD Sales =
CALCULATE (
    SUM ( 'bi sales'[TotalLineSales] ),
    FILTER (
        ALL ( DateTable ),
        YEAR ( DateTable[Date] ) = YEAR ( TODAY () )
            && DateTable[Date] <= TODAY ()
    )
)
LY YTD Sales =
CALCULATE (
    SUM ( 'bi sales'[TotalLineSales] ),
    FILTER (
        ALL ( DateTable ),
        YEAR ( DateTable[Date] )
            = YEAR ( TODAY () ) - 1
            && DateTable[Date]
                <= TODAY () - 365
    )
)

 

Regards

How do I alter this measure to account for a fiscal year?  My year starts on October 1st.

Hi,

 

Share a dataset and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Data SetData SetYTD MeasureYTD MeasureExpected ResultsExpected ResultsActual ResultsActual Results

The problem I'm having is when I pull YTD it's looking at camp registrations starting on Jan 1.  I want my year to start on Oct 1.  I've tried using this measure _YTD = TOTALYTD(sum(Registrations[Quantity]),'Dim Date'[Date Key],"10-01") and I don't like the fact that I have to add the date key to the filter and adjust it everyday.  I need something that gives me the YTD and PYTD numbers that start the calculation of Oct 1.  This report needs to refresh every morning and I can't be there to change the date key every day so the management team can see how we're pacing compared to prior year.

 

Any help would be greatly appreciated.

Hi @Anduve11,

 

Share the link from where i can download your Excel file.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

I'm not sure how to do that.  I tried to attached the excel spreadsheet but it wouldn't let me.

Hi,

 

Upload your workbook to Google Drive and share the download link here.  Else, try to paste the data from your Excel file here.  For the data that you share, please show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.