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!

theyk's avatar
theyk
Regular Visitor
4 years ago

The ask was different :D. Wanted to get the data for the range between 1st of previous month and 1st of the current month. 

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!