Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi All,
I have a need to create a range of measures using YOY dates using a custom calendar where days of week always align. This is a seasonal tourism business which is extremely weekend dominant. If i use the built in SAMEPERIODLASTYEAR or PARALLELPERIOD I will end up comparing like this (Just a random date selection based on 19th July).
Sun 2015-07-19
Tue 2016-07-19
Wed 2017-07-19
Thu 2018-07-19
Fri 2019-07-19
In the Date Table I have a DayOfYear which aligns the years correctly like this (Day of year = 200).
Sun 2015-07-19
Tue 2016-07-19
Wed 2017-07-19
Thu 2018-07-19
Fri 2019-07-19
Here is an example of one of the measures I have created is here but I have a couple of questions
PYTotalSalesYTD:= IF( HASONEVALUE(TransactionDate[YearNumber]), CALCULATE( SUM(FactTransactionProduct[HomeGrossAmount]), FILTER( ALL(TransactionDate), TransactionDate[YearNumber]=VALUES(transactiondate[YearNumber])-1 && TransactionDate[DayOfYear]<=max(TransactionDate[DayOfYear]) ) ), BLANK() )
Solved! Go to Solution.
hi, @Anonymous
1. Try to change ALL(TransactionDate) to ALLSELECTED(TransactionDate)
2. If you could try this formula as below:
PYTotalSalesYTD := IF ( HASONEVALUE ( TransactionDate[YearNumber] ), CALCULATE ( SUM ( FactTransactionProduct[HomeGrossAmount] ), FILTER ( ALLSELECTED ( TransactionDate ), CONTAINS ( VALUES ( TransactionDate[DayOfYear] ), TransactionDate[DayOfYear], DATE ( YEAR ( TransactionDate[DayOfYear] ) - 1, MONTH ( TransactionDate[DayOfYear] ), DAY ( TransactionDate[DayOfYear] ) ) ) ) ), BLANK () )
If it is not your case, please share pbix file or some data sample and expected output. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
Hi @Anonymous
I would consider a structure like this for your PYTotalSalesYTD measure:
PYTotalSalesYTD := IF ( HASONEVALUE ( TransactionDate[YearNumber] ), VAR CurrentYear = SELECTEDVALUE ( TransactionDate[YearNumber] ) VAR MaxDayOfYear = MAX ( TransactionDate[DayOfYear] ) VAR YearDayFilter = TREATAS ( GENERATE ( { CurrentYear - 1 }, GENERATESERIES ( 1, MaxDayOfYear ) ), TransactionDate[YearNumber], TransactionDate[DayOfYear] ) RETURN CALCULATE ( SUM ( FactTransactionProduct[HomeGrossAmount] ), ALL ( TransactionDate ), YearDayFilter ), BLANK () )
I've split the logic into a few variables and YearDayFilter is the final filter applied to YearNumber & DayOfYear. This should be reasonbaly efficient as it just filters the required columns of TransactionDate and uses GENERATESERIES to create the list of DayOfYear values.
Does this give the correct result?
Regards,
Owen
Thanks so much to both of you, my first time posting was well worth it!
I have a feeling both solutions would work but @v-lili6-msft solution worked immediatelly. @OwenAuger , the measure errored which i'm sure is just a result of typing a measure in the absence of any actual data (Error was "Function GENERATE does not allow two columns with the same name "[VALUE]). I assume i need to get an alias into the formula somehow. The concepts in that measure blew my mind, ive been googling tring to fix the error and understand the structure of the YearDayFilter variable. I didn't realise you could assign a filter to a variable and didn't know about GENERATE or GENERATESERIES.
Do you think the general concept of using the YearNumber and custom DayOfYear an acceptably efficient solution to the problem I have?
@Anonymous
My mistake - the two tables within GENERATE each had a column named Value.
Correction below 🙂
PYTotalSalesYTD := VAR CurrentYear = SELECTEDVALUE ( TransactionDate[YearNumber] ) RETURN IF ( NOT ISBLANK ( CurrentYear ), VAR MaxDayOfYear = MAX ( TransactionDate[DayOfYear] ) VAR YearDayFilter = TREATAS ( GENERATE ( SELECTCOLUMNS ( { CurrentYear - 1 }, "YearFilter", [Value] ), SELECTCOLUMNS ( GENERATESERIES ( 1, MaxDayOfYear ), "DayOfYearFilter", [Value] ) ), TransactionDate[YearNumber], TransactionDate[DayOfYear] ) RETURN CALCULATE ( SUM ( FactTransactionProduct[HomeGrossAmount] ), ALL ( TransactionDate ), YearDayFilter ), BLANK () )
hi, @Anonymous
1. Try to change ALL(TransactionDate) to ALLSELECTED(TransactionDate)
2. If you could try this formula as below:
PYTotalSalesYTD := IF ( HASONEVALUE ( TransactionDate[YearNumber] ), CALCULATE ( SUM ( FactTransactionProduct[HomeGrossAmount] ), FILTER ( ALLSELECTED ( TransactionDate ), CONTAINS ( VALUES ( TransactionDate[DayOfYear] ), TransactionDate[DayOfYear], DATE ( YEAR ( TransactionDate[DayOfYear] ) - 1, MONTH ( TransactionDate[DayOfYear] ), DAY ( TransactionDate[DayOfYear] ) ) ) ) ), BLANK () )
If it is not your case, please share pbix file or some data sample and expected output. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |