SQL Windowed function in DAX

[FredCarmoSilva]

Hi all,

I am finding quite hard to get to grips with the different contexts in PBI and creating some "windowed functions". Hopefully the resolution to this problem will push me closer to being proficient in PBI.

I have something like the Data below, where the months shown come from a Slicer (Last 3 Months(Calendar) for example.

I can calculate the coloured cells, but am struggling to aggreagate the apples sold by each store on the months shown (as per the Count apples sold selected months column).

Please note that Count apples sold is actually a Count of transactions, it is effectively a DISTINCTCOUNT(TransactionID).

Any help would be greatly appreciated.

Regards,

Fred

[Tahreem24]

 @FredCarmoSilva ,

Please create a below measure and check you result:

Count Apple sold each month = CALCULATE(Sum('Table'[Count Apple Sold]),ALLEXCEPT('Table','Table'[Store Name]))

Please give KUDOS and accept this as a solution if it helps you!

[FredSilva]

@Tahreem24 

Thanks for your answer.

It didn't do what I was looking for though, what happened with what you suggested is that now I have some Stores that appeared although they didn't have any transcation on the 3 months shown on the table.

I will try to add more info so you can get a better idea of what I am working with.

Maybe think of Count apples sold as being Count of transactions because effectively that is what it is, is a Measure doing a DISTINCTCOUNT('Table'[Transaction_ID]).

My months are coming from a Slicer, so effectively that are more many months of data, but I am Slicing/Filtering to 3 months only.

[FredSilva · Accepted solution]

For anyone who might be looking for a solution, this ended up being quite simple. 

Given that I had a Date[TransactionDate] as a Slicer, and the Month show on the table was Date[TransactionMonth] what I was doing wrong was trying to get ALLSELECTED( Date[TransactionMonth]  ) and should have done ALLSELECTED( Date ). 

Solution was to do :

Number of Transactions per Store on Shown Months = CALCULATE(DISTINCTCOUNT(TransactionID), ALLSELECTED( Date ))