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

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

Hello @matemusic,

 

Great solution.

 

Thanks!

mravestein Frequent Visitor
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"

 

MonthYearRelation.png

 

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

 

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

where does the  [Lastdate] comes from?

Highlighted
rajendrav2016 Frequent Visitor
Frequent Visitor

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

What is the Year Month calculation?

ignas Regular Visitor
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
 

matemusic Regular Visitor
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)
Ashwin_patil Frequent Visitor
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