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.
Hi, everyone
Please, help!!!!!
The matrix is composed by:
Rows:
WeekCount --> Number of week regardless year (starts 01/01/2010)
Rel.Week --> Relative week respect current one
Snapshot date --> Snapshot date
Columns:
Sales month --> Month where sales have been occurs
Values:
Sales amount --> Amount for sales.
LastDay --> Last day weeks selected
I want to consider only sales where sales date > Last snapshot selected. To do that I think must fix lastday to the last snapshot selected regardless row context.
In the example I want to show only sales where sales date > 06/06/2016 for every snapshot date.
Is there any way to do that?.
Thanks in advance,
Check out ALL and ALLEXCEPT functions, these remove all context and remove all except selected context respectively.
Hi, @Greg_Deckler
Many thanks for your quick response.
I have tried do it in different ways whithout desired result.
In the image below, there is three dates.
MaxDate 1 = CALCULATE(MAX('Dimdate'[Snapshot date]);ALL('Dimdate'))
MaxDate 2 = CALCULATE(MIN('Dimdate'[Snapshot date]);ALLEXCEPT(Dimdate;Dimdate[WeekCount]))
MaxDate 3 = CALCULATE(MIN('Dimdate'[Snapshot date]);ALL(Dimdate[WeekCount]) )
However, measures don't return max date I need. In this case It has to be 23/05/16 for every row context.
If I choose week count from 340 to 342, it has to be 30/05/16 for every snapshot date.
Any tip?
Thanks!!!!
Hi @Angel,
Based on my understanding, I think the formula below should work in your scenario.
MaxDate =
CALCULATE (
MAX ( 'Dimdate'[Snapshot date] );
ALLEXCEPT ( Dimdate; Dimdate[WeekCount] )
)
Or
MaxDate = CALCULATE ( MAX ( 'Dimdate'[Snapshot date] ); ALL ( Dimdate[Snapshot date] ) )
If that is not the case, could you post your table structures with some sample data? So that we can help further investigate on the issue. It's better to share a sample pbix file. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Regards
Hi, @v-ljerr-msft
It works!!!!!!.... Thank you, master!!!!!
The last thing I want to do is only consider sales where sales date > max fecha. To do that, I have tried with this phormula..
Sales amount after max snapshot = CALCULATE(MAX('Sales registered'[Sales amount]);FILTER('Sales registered';'Sales registered'[Sales Date] > [MaxDate]))
However, as you can see in the image above It doesn´t work...
Any tip?
Hi, @v-ljerr-msft
As you have said before, I include a link with pbix file.
The goal is only consider sales where sales date > last snapshot date selected in slicers.
Many thanks for your help,
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 |
---|---|
107 | |
98 | |
77 | |
66 | |
53 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |