Forum Discussion

theyk's avatar
theyk
Regular Visitor
4 years ago
Solved

Create Monthly reports with Dynamic dateTime filter

I have an orders dataset, where I have the following fields   order_datetime  order_id settlement_datetime amount country order_type   I wanted to create a subscription so that on 15th of ev...
Need Help
  • YukiK's avatar
    YukiK
    4 years ago

    Then this should do it:

     

    Your table =

    VAR __FirstDayOfThisMonth = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
    VAR __FirstDayOfLastMonth = EDATE ( DATE(YEAR(TODAY()), MONTH(TODAY()), 1), 1)

    RETURN
    FILTER (
    financials,
    AND (
    financials[Date] >= __FirstDayOfLastMonth,
    financials[Date] <= __FirstDayOfThisMonth
    )
    )

     

    Hope that helps!

YukiK's avatar
YukiK
Impactful Individual
4 years ago

Then this should do it:

 

Your table =

VAR __FirstDayOfThisMonth = DATE(YEAR(TODAY()), MONTH(TODAY()), 1)
VAR __FirstDayOfLastMonth = EDATE ( DATE(YEAR(TODAY()), MONTH(TODAY()), 1), 1)

RETURN
FILTER (
financials,
AND (
financials[Date] >= __FirstDayOfLastMonth,
financials[Date] <= __FirstDayOfThisMonth
)
)

 

Hope that helps!

theyk's avatar
theyk
Regular Visitor
4 years ago

cool. Have a follow-up question,  Let's say I refresh data every 15 days the newly filtered table will also be updated right? 

 

Also, Taking this opportunity, I would like to subtract the timeZone offset for __FirstDayOfThisMonth & __FirstDayOfLastMonth. For the offset mapping I have created a table (named timeZone)  that has the columns country_code and offset  . 

This is how the table looks like:

SG  8

IN   5.5

I can get the country_code from parameters or even hardcode should not be a problem . Is there a way I get the offset by using second table

Eg: I want to do something like this

 

VAR timeZoneOffset =  timeZoneTable['SG']

VAR __FirstDayOfThisMonth = DATE(YEAR(TODAY()), MONTH(TODAY()), 1) - duration(0,timeZoneOffset,0,0)

  • YukiK's avatar
    YukiK
    Impactful Individual
    4 years ago

    "Have a follow-up question, Let's say I refresh data every 15 days the newly filtered table will also be updated right? " -> Yes. Calculcated tables get updated every time the data model refreshes.

     

    You may be able to do that using duration() but here is a simpler version to get the result (this is a calculated column):

     

    If this helps, please give it a thums up!