Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
chennamalluvija
New Member

I want show the Last year last 3 months sales when i select current year in slicer .

Hi Team,
I have finacials table. 
I want to get the last year last 3 months sales value in my kart when i wan select the current year in slicer.
Example- I have data Starting from Jan 2022 to Dec 2023. When i select the 2023 in slicer ,i want see the sum of Oct,Nov,Dec value of 2022 in my kart.

chennamalluvija_0-1683037343901.png

 

1 ACCEPTED SOLUTION
eliasayyy
Super User
Super User

i think i aswered wrong , you want if you press on 2023 to return 2022 last 3 months?

ok this is kinda tricky 

Step 1 create a disconnected date table(date table without relationships)

step 2 add the dates you want to the table visual from the disconnected date tabel

step 3 create a filter measure that will show you your desire dates which are last 3 month of previous year

Filtering Dates = 
VAR _m = CALCULATE(MAX('Table'[Month ID]),ALLEXCEPT('Table','Table'[Year]),'Table'[Year] = SELECTEDVALUE('Table'[Year])-1)
VAR weekstart = CALCULATE(MIN('Table'[Date]),REMOVEFILTERS('Table'),'Table'[Month ID] >= _m-2)
VAR weekend = CALCULATE(MAX('Table'[Date]),REMOVEFILTERS('Table'), AND('Table'[Month ID] = _m,'Table'[Month ID] >= _m-2))
VAR result = IF(AND(MIN('Disconnected Table'[Date]) >= weekstart, MAX('Disconnected Table'[Date]) <= weekend), 1, 0)
RETURN
result





insert this measure into your filter panel of your table vissual where you have dates from disconnected table and set it to is 1

annonymous1999_0-1683042086218.png

 

step 4

create a new sum measure 

Sales Sum2 = CALCULATE(SUM('Table'[Sales]),REMOVEFILTERS('Table'),'Table'[Date] = MAX('Disconnected Table'[Date]))


 

annonymous1999_1-1683042143947.png

 

if you want to make it by month-year

chnage the date of disconnected table to month year 

and change the sales measure to 

Sales Sum2 = CALCULATE(SUM('Table'[Sales]),REMOVEFILTERS('Table'),'Table'[Month ID] = MAX('Disconnected Table'[Month ID]))




annonymous1999_2-1683042227031.png

 

if it helped please leave a kudos

View solution in original post

2 REPLIES 2
eliasayyy
Super User
Super User

i think i aswered wrong , you want if you press on 2023 to return 2022 last 3 months?

ok this is kinda tricky 

Step 1 create a disconnected date table(date table without relationships)

step 2 add the dates you want to the table visual from the disconnected date tabel

step 3 create a filter measure that will show you your desire dates which are last 3 month of previous year

Filtering Dates = 
VAR _m = CALCULATE(MAX('Table'[Month ID]),ALLEXCEPT('Table','Table'[Year]),'Table'[Year] = SELECTEDVALUE('Table'[Year])-1)
VAR weekstart = CALCULATE(MIN('Table'[Date]),REMOVEFILTERS('Table'),'Table'[Month ID] >= _m-2)
VAR weekend = CALCULATE(MAX('Table'[Date]),REMOVEFILTERS('Table'), AND('Table'[Month ID] = _m,'Table'[Month ID] >= _m-2))
VAR result = IF(AND(MIN('Disconnected Table'[Date]) >= weekstart, MAX('Disconnected Table'[Date]) <= weekend), 1, 0)
RETURN
result





insert this measure into your filter panel of your table vissual where you have dates from disconnected table and set it to is 1

annonymous1999_0-1683042086218.png

 

step 4

create a new sum measure 

Sales Sum2 = CALCULATE(SUM('Table'[Sales]),REMOVEFILTERS('Table'),'Table'[Date] = MAX('Disconnected Table'[Date]))


 

annonymous1999_1-1683042143947.png

 

if you want to make it by month-year

chnage the date of disconnected table to month year 

and change the sales measure to 

Sales Sum2 = CALCULATE(SUM('Table'[Sales]),REMOVEFILTERS('Table'),'Table'[Month ID] = MAX('Disconnected Table'[Month ID]))




annonymous1999_2-1683042227031.png

 

if it helped please leave a kudos

eliasayyy
Super User
Super User

hello,

step 1 : add month ID in new column

Month ID = 
VAR _min = YEAR(MIN('Table'[Date]))
RETURN
MONTH('Table'[Date]) + (YEAR('Table'[Date]) - _min) *12



step 2 make measures:

Sales Sum = SUM('Table'[Sales])

 

LAST 3 month sales = 
VAR _m = CALCULATE(MAX('Table'[Month ID]),ALLEXCEPT('Table','Table'[Year]))
RETURN
CALCULATE(
    [Sales Sum],
    FILTER('Table',
    'Table'[Month ID] >= _m -2))

 

annonymous1999_0-1683040045719.png

 

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors