Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Default Date Values

Hi Power BI community! :), this is my first post - So I have the following question:  In my requirments I need the user to directly see the previous month in the dashboard. This means that I ne...
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, Anonymous

    You may try this way as below:

    Add a column for date column by this formula

    Column = IF( DATEDIFF('Date'[Date],TODAY(),MONTH)=1,"previous month")

    Then drag this field into a slicer or page level filter to filter the data.

     

    Best Regards,

    Lin

v-lili6-msft's avatar
v-lili6-msft
Community Support
7 years ago

hi, Anonymous

Column = IF( DATEDIFF('Date'[Date],TODAY(),MONTH)=1,"previous month","not previous month")

You could drag this field into a slicer do not drag it into visual\ page\ report  level filter to filter the data.

If so when don't select any field or select all fields into slicer, they can change the reporting period optionally, and if only

select "previous month", It will just previous month data.

 

Best Regards,

Lin

Anonymous's avatar
Anonymous
Not applicable
7 years ago

hi v-lili6-msft,

yes when putting the calculated column in the slicer that works. Previous month returns me all the values of November (Now its Dec.), which is exactly what I want. 

I would also like the user to have the option to do the same thing for the previous year.  I noticed that when I use the datediff function it returns all the values in the last year. At least when I filter in my data view by previous year it only shows me all 365 days of 2017. What I would like to have is a date range of 06.12.2017 - 30.11.2018. So the current month should not be included in this filter. 

I used the following Dax for the previous year:

Previous Year = IF(
    DATEDIFF(
        'Time Dimension'[Date]; SELECTEDVALUE(
            'Time Dimension'[Date (bins)]; TODAY()); YEAR) = 1; "previous year")

Maybe it is important to note that due to the granularity in my data, the date are only delivered in months.  So to be able to calculate with them I binned my time dimension by Months, and made a new date in all my tables by adding a "01" to my date columns. 

Any ideas what would be a good way to tackle this?

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    hi, Anonymous

    Your formula doesn't work for calculate column.

    try the formula like this 

     

    Column = IF( DATEDIFF('Date'[Date],TODAY(),MONTH)=1,"previous month",IF( DATEDIFF('Date'[Date],TODAY(),MONTH)=13,"previous year previous month","not"))

    then drag it to slicer, you could select previous month "2018/11", previous year previous month "2017/11" and others.

     

    of course, you could continue to add conditional like:

     DATEDIFF('Date'[Date],TODAY(),MONTH)=25

     DATEDIFF('Date'[Date],TODAY(),MONTH)=37, ect.

     

    also, you could use this formula to get all the previous month based on current month for each year.

    Column 2 = IF(( DATEDIFF('Date'[Date],TODAY(),MONTH)-DATEDIFF('Date'[Date],TODAY(),YEAR)*12)=1,"previous month","not previous month")

    for example:

     Previous month returns me all the values of November for each year (Now its Dec.).

     

    Best Regards,

    Lin