cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

Re: Show last 13 months based on user single slicer selection

Hello @matemusic,

Great solution.

Thanks!

Highlighted
Frequent Visitor

Re: Show last 13 months based on user single slicer selection

Thanks for the solution!

I tried to apply your solution, but with 1 slicer that has the Month+Year combined. It did not work for some reason.

I still got it working tough by creating 2 tables

Table1:

Month  - "01"

Year      - "2017"

MonthYear - "201701"

Table2:

Month - "01"

Now I can use the YearMonth column in my slicer and your solutions still does the rest

Visitor

Re: Show last 13 months based on user single slicer selection

where does the  [Lastdate] comes from?

Frequent Visitor

Re: Show last 13 months based on user single slicer selection

What is the Year Month calculation?

Regular Visitor

Re: Show last 13 months based on user single slicer selection

Hey @matemusic

You have really an excellent solution.

I need to have 2 tables:

1. First table: Should show exactly what you created (it works).

2. Second table: If month selected then it should only show that month. If month is not selected then it should show all dates for that year.

```Sum of profit (month) =
var Ldate = date(max('Year'[Year]);max('Month'[Month]);1) //Last date

var Fdate = EDATE(Ldate;0) //First date

var SumProfit = sum(traffic[Amount]) //Calculation

return

IF(HASONEVALUE('Month'[Month]);
if(min('Date table'[Date])<fdate;
blank();
if(min('Date table'[Date])>Ldate;
blank();
SumProfit));
[profit])```

I get right with the month part, but I cannot find out how I can find out the year part. Do you have any ideas?
Here is the file

Regular Visitor

Re: Show last 13 months based on user single slicer selection

Hi, maybe this will help. I added new coumn Year in the calendar table. Bold blue text in the code represent new code

```Sum of profit (month) =
var Ldate = date(max('Year'[Year]);max('Month'[Month]);1) //Last date

var Fdate = EDATE(Ldate;0) //First date
VAR SYear = CALCULATE(max('Year'[Year]);ALLSELECTED()) -- it can be without calculate and allselected, i am just used to work in that way
VAR SumProfitYear = CALCULATE([profit];'Date table'[Year]=SYear)
var SumProfit = sum(traffic[Amount]) //Calculation
return
IF(HASONEVALUE('Month'[Month]);
if(min('Date table'[Date])<fdate;
blank();
if(min('Date table'[Date])>Ldate;
blank();
SumProfit));
SumProfitYear)```
Frequent Visitor

Re: Show last 13 months based on user single slicer selection

Hello,

I found this very helpful. now can you please help me how to show the selected month value in card visual.(without connecting year table and date table)? i use yyyymmm slicer... am using year and month from one table itself