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

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Brij
Helper I
Helper I

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

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:

 

TVSOrder
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 Range6

 

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
2 REPLIES 2
3CloudThomas
Super User
Super User

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.>

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 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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