How to get last 12 weeks, 12 months, 4 quarters data based on filter selection?

[Brij]

Hi all,

I have a requirement to show data based on selection (week to date, month to date, financial quarter to date, financial half year to date and financial year to date) on a card visual as well as on the bar chart that shows last 12 weeks (for week to date), last 12 months (for month to date), last 4 financial quarter (for financial quarter todate) etc.

I have managed to create a measure that give meaccurate results for the card visuals however am struggling to get the same on the column chart that shows last 12 weeks (for WTD), last 12 months (for MTD) etc. data!

Here what I have done so far:

A stand alone time series table as per below:

TVS	Order
WTD (Week To Date)	1
MTD (Month To Date)	2
FQTD (Financial Quarter To Date)	3
FHYTD (Financial Half Year To Date)	4
FYTD (FInancial Year To Date)	5
Custom Date Range	6

Calendar dimention table:

In my Calendar dimention table I have created columns that returns, WTD - Start Date, MTD - Start Date and so on that gives me a start dates based on the current date. 

Fact table:

My fact table is in active relation with Calendar table via chk date to date column in the Calendar table. This chk date column has the current date.

I need to get the count of total applications based on its capture date that is also available in my fact table.

I have created a flag within my fact table as per below:
IsWTD flag = if(capture date >= RELATED(CalendarTable[WTD - Start Date]) && capture date <= RELATED(CalendarTable[TimeSeries - End]),"Y","N")
The above flag will be used in a mesure that execute the count as per below:

sub_presub_output =

var tsselected = SELECTEDVALUE(TimeSeriesValues[TSV])

var sub_presub_WTDCount =
               calculate(DISTINCTCOUNT(FactTable[applncation_key]),
                            FactTable[Captr Date] <> BLANK()
                        &&
                      FactTable[IsWTD flag] = "Y")

The above measure gives me correct result in my card visual, however I am not able to show it on the bar chart that shows last 12 weeks (for WTD), last 12 months (for MTD), last 4 financial quarter (for FQTD), etc.

Any help is appreciated.

Thank you,

Brijesh

[3CloudThomas]

first, try to use the filter pane for each visual and select the date. There is a relative date function in the filter view pane to help select last x <week,month, etc.>

[Brij]

Thanks @3CloudThomas

My time series table is a stand alone table and not linked with the date table. My measeure giving me a correct overall result but it is not being split out for the last 12 weeks! I tried using a relevant date from my fact table and use it as filter to user relative date function but still no luck!

Please advise. 

Thanks