Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
My question is let's say today is 5/10/2022
I'm trying to get average of recent sale. I have calendar table, I know which one is sold so I can filter those.
I just dont know how to get the latest sale for example today ? then bring only that
not today check yesterday ? then bring only that
not yesterday previos day ?
I can't find any logic to write it down.
Let me know If there's any questions
Solved! Go to Solution.
Hi @Anonymous ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a calculated column as below to get the week number
Week = WEEKNUM('Table'[Date],2)
2. Create two measures as below to get the recent sales and diff between current week and prior week
Recent sale =
VAR _selprod =
SELECTEDVALUE ( 'Table'[Product] )
VAR _maxdate =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Product] = _selprod )
)
VAR _recentsale =
CALCULATE (
MAX ( 'Table'[Sales] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Product] = _selprod
&& 'Table'[Date] = _maxdate
)
)
RETURN
_recentsale
Diff =
VAR _selweek =
SELECTEDVALUE ( 'Table'[Week] )
VAR _selprod =
SELECTEDVALUE ( 'Table'[Product] )
VAR _preweek =
CALCULATE (
MAX ( 'Table'[Week] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Product] = _selprod
&& 'Table'[Week] < _selweek
)
)
VAR _curweeksales =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER ( 'Table', 'Table'[Product] = _selprod && 'Table'[Week] = _selweek )
)
VAR _preweeksales =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Product] = _selprod
&& 'Table'[Week] = _preweek
)
)
RETURN
IF ( ISBLANK ( _preweeksales ), BLANK (), _curweeksales - _preweeksales )
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Thank you for your help but I guess "earlier" won't be right implemented because I couldn't run it on my end.
Hi @Anonymous ,
What you are trying to create is a calculated column or a measure? If it is a measure, the EARLIER function cannot be applied to the measure. EARLIER is mostly used in the context of calculated columns. In order to give you a suitable solution quickly, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Suppose I need two measures actually,
One is the product is released prior week
two is the product is recently sold
and i will need to find difference between those two measures in the same product code, where they are sold.
Unfortunately, I cannot share any file here.
Hi @Anonymous ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want.
1. Create a calculated column as below to get the week number
Week = WEEKNUM('Table'[Date],2)
2. Create two measures as below to get the recent sales and diff between current week and prior week
Recent sale =
VAR _selprod =
SELECTEDVALUE ( 'Table'[Product] )
VAR _maxdate =
CALCULATE (
MAX ( 'Table'[Date] ),
FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Product] = _selprod )
)
VAR _recentsale =
CALCULATE (
MAX ( 'Table'[Sales] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Product] = _selprod
&& 'Table'[Date] = _maxdate
)
)
RETURN
_recentsale
Diff =
VAR _selweek =
SELECTEDVALUE ( 'Table'[Week] )
VAR _selprod =
SELECTEDVALUE ( 'Table'[Product] )
VAR _preweek =
CALCULATE (
MAX ( 'Table'[Week] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Product] = _selprod
&& 'Table'[Week] < _selweek
)
)
VAR _curweeksales =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER ( 'Table', 'Table'[Product] = _selprod && 'Table'[Week] = _selweek )
)
VAR _preweeksales =
CALCULATE (
SUM ( 'Table'[Sales] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Product] = _selprod
&& 'Table'[Week] = _preweek
)
)
RETURN
IF ( ISBLANK ( _preweeksales ), BLANK (), _curweeksales - _preweeksales )
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi,
Sorry for late response,
I really appreciate your effort and explanation I will look at the pbix file now.
Even though it's not the right approach to my question, i want to understand the logic you work on here.
I think depending on the business requirements has been updated, and interpreting here online wasn't so clear I couldn't ask my need properly.
but, it turns out way more complicated what i was expressing.
because of the level difference now i'm not working on this task, but still if you want to know the whole idea I can give all the informations yet I can't share any data due to business confidentiality.
again I respect your time and effort.
Best
Maybe something like this?
Hey,
Yes that's what I exactly wrote it down
but I want to make sure that I'm getting the 'current sale' with this measure...
which columns or filtering that i should apply to get a narrow result so, I can see it clearly ?
I have columns such as saledate, IsSold etc.
Hi @Anonymous ,
You can refer the following links to get the sales with recent date...
Find Sales on the Last Date with DAX in Power BI
Show Value for the Last Date in Microsoft Power BI
Calculating latest date of sale
DateofLastSale =
CALCULATE (
MAX ( Table1[DateOfSale] ),
FILTER (
ALLEXCEPT ( Table1, Table1[EmployeeID] ),
Table1[DateOfSale] < EARLIER ( Table1[DateOfSale] )
)
)
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
39 | |
33 | |
18 | |
17 |