Forum Discussion

snusseri's avatar
snusseri
Data Storage
10-22-2023

YTD calculation by any date selected on a date filter

Hello,

I have a  basic requirement but I am not sure how to implement it.

My dashboard shows sales by a filtered time frame (can be Month, Quarter OR year).

To the Total Sales widget (attached below) I would like to add, on the secondary measure, the Year-To-Date total sale according to the year selected in the date filter. I would like it to be dynamic as follows:

If the user selects 'Q4-2023' I would like to show, on the secondary measure, the total 2023 sales.
if the user selects Q2-2022' I would like to show,on the secondary measure, the total 2022 sales.
Please note, that the date filter is a dashboard filter that impacts all dashboard widgets.
*In the screenshot attached I was able to use a formula filter but on a predefined year, Its not dynamic. 

Please help. I am sure someone already encountered this basic requirement.
Thanks
Sagi

 

dashboards & reporting

1 Reply

Replies have been turned off for this discussion
  • Benji_PaldiTeam's avatar
    Benji_PaldiTeam
    Data Pipeline
    12-01-2023

    Hi snusseri ,

    If you are open to using the Blox Plugin, you can achieve the desired outcome by following these steps:

    1. Create a Blox widget using the provided script: 

    {
    "style": "",
    "script": "",
    "title": "",
    "showCarousel": true,
    "body": [
        {
            "spacing": "medium",
            "type": "Container",
            "items": [
                {
                    "spacing": "small",
                    "type": "TextBlock",
                    "text": "Total Sales ",
                    "color": "default",
                    "horizontalAlignment": "center"
                },
                {
                    "type": "TextBlock",
                    "text": "{panel: Total Cost}",
                    "horizontalAlignment": "center",
                    "size": "extraLarge",
                    "style": {
                        "color": "#117899"
                    }
                },
                {
                    "type": "TextBlock",
                    "text": "Total sales in the Year: <b>{panel: YTD Cost}</b>",
                    "horizontalAlignment": "center",
                    "size": "medium",
                    "style": {
                        "color": "#000000",
                        "fontSize": "14px"
                    }
                }
            ]
        }
    ],
    "actions": []
}

    2. Include a date dimension under the Items panel, ensuring that the data level is set to 'Year'.

    3. Add two measures under the Values panel. The first measure should use the actual formula, such as 'sum([Cost])'. For the second measure, use a formula like this: (ytdsum([Total Cost]), all([Quarters in Date]), ALL([Months in Date]), all([Days in Date]), all([Weeks in Date])). This setup ignores date filters at the Quarter, Month, Week, and Day levels, considering only the Year since it's specified in the Items panel.

    Upon completion, your Blox should resemble something like this: 

     

    Feel free to reach out if you have further questions, we're always happy to help 🙂
    [email protected] 
    Paldi Solutions